Oracle triggers implement warehouse receiving and picking

Source: Internet
Author: User
Warehouse receiving and receiving functions are available in many systems. May be called differently. Some may be the quantity, and some may be the amount. Here, I take the amount as an example to implement warehouse receiving and warehouse receiving in many ways. One is implemented in languages and the other is implemented using triggers. They have their own merits.

Warehouse receiving and receiving functions are available in many systems. May be called differently. Some may be the quantity, and some may be the amount. Here, I take the amount as an example to implement warehouse receiving and warehouse receiving in many ways. One is implemented in languages and the other is implemented using triggers. They have their own merits.

Implemented in languages
Benefits:
1. reduce access to the database.
2. Good portability.
Disadvantages:
1. There are many things to consider in operations. To modify one place, you must modify the other. That is, they are interrelated. If a change is missing, data inconsistency may occur.
Implement with trigger
Benefits:
1. developers can be freed from the complex associations and focus on complicated businesses.
Disadvantages:
1. Poor portability.
Next I will use an example to implement a simple warehouse receiving and picking operation. This is because there are few fields used in the example table. The example here is only for reference.
The data table is the warehouse receiving amount table (hereinafter referred to as the warehouse receiving table) income, the warehouse picking amount table (hereinafter referred to as the warehouse picking table) outlay, and the balance table balance
The Code is as follows:
Income {
Id number;
Pay_amount number; (warehouse receiving amount field)
}
Outlay {
Id number;
Outlay_amount number; (outbound amount field)
}
Balance
{
Id number;
Balance number; (balance field)
}

Create triggers in the warehouse receiving and warehouse receiving tables
Income ):
The Code is as follows:
Create trigger "AA". "TRI_ADD" AFTER
INSERT
Or delete on "INCOME" for each row begin
If deleting then
Update balance set balance = nvl (balance, 0)-: old. pay_amount;
Elsif updating then
Update balance set balance = nvl (balance, 0)-: old. pay_amount +: new. pay_amount;
Else
Update balance set balance = nvl (balance, 0) +: new. pay_amount;
End if;
End;

Outbound table (outlay ):
The Code is as follows:
Create trigger "AA". "TRI_CUT" AFTER
INSERT
OR DELETE
Or update on "OUTLAY" for each row begin
If deleting then
Update balance set balance = nvl (balance, 0) +: old. outlay_amount;
Elsif updating then
Update balance set balance = nvl (balance, 0) +: old. outlay_amount-: new. outlay_amount;
Else
Update balance set balance = nvl (balance, 0)-: new. outlay_amount;
End if;
End;

I will explain it below
Oracle triggers. trigger events include insert, delete, and update columns, which correspond to the inserting, deleting, and updating keywords respectively.
You can use the if statement to implement
The Code is as follows:
If inserting then
-----
Elsif updating then
-----
Elsif deleting then
------
End if;

NVL (eExpression1, eExpression2)
If the calculation result of eExpression1 is null, NVL () returns eExpression2.
If the calculation result of eExpression1 is not null, eExpression1 is returned. EExpression1 and eExpression2 can be any data type.
If both eExpression1 and eExpression2 are null values, NVL () returns. NULL ..
Insert and delete are not mentioned here. The update operation mainly involves the update operation. Note that the update operation should first subtract the old value and add the new value.
The above is the implementation of the trigger example. Please forgive me for writing your article badly.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.