Oracle triggers implement warehouse receiving and picking

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
Copy codeThe 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 ):
Copy codeThe 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 ):
Copy codeThe 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
Copy codeThe 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.