About the unique constraint on the violation of a primary key caused by a trigger to an extended table insert

Today, when you write a row-level update trigger, the trigger triggers an insert operation on the extended table Tab_b when the table tab_a is updated. The problems encountered are not generally more.

Insert into Tab_a (COL_A1) Select Col_b1 from Tab_b where col_b1= in the block portion of the trigger: NEW.COL_B1;

Tab_a is the extension table for the trigger, Tab_b is the trigger's table of actions. After the trigger is written, the compilation succeeds, and then executes: Update tab_b where col_b2=xxxx;

Error: "The table has changed, the trigger or function can not read it";

Problem Analysis:

When you execute a DML statement in Oracle, you need to display a commit operation. When updates are made, triggers are triggered to perform actions on the trigger table and the extended table, but the UPDATE statements and triggers are in the same transaction management, so we can't do any additional action on the trigger table without the UPDATE statement being committed. If additional actions are performed, the exception information is thrown as above.

Solution idea:

1. The reason for the error is that the trigger and DML statements are in the same transaction management, so scenario one is to split the triggers and DML statements into two separate transactions. Pragma autonomous_transaction can be used here; Tells Oracle triggers to be custom transactions.

CREATE trigger or replace Tab_b_tri

After update on Tab_b
For each row
Declare--This is the key place to specify custom transactions, where the variable is declared.
pragma autonomous_transaction;
Begin

Insert into Tab_a (COL_A1) Select Col_b1 from Tab_b where col_b1=: new.col_b1;--here: NEW.COL_B1 is the updated column value of the update operation

--Here you need to show the commit transaction
Commit The existence of rollback and commits is not allowed in a general trigger. It's a custom thing.

end Tab_b_tri ;

2.2, in Oracle Trigger: New,:old Two special variables, when the trigger is a row-level trigger, the trigger provides new and old two special variables to hold the temporary row data, we can remove the data from two special variables to perform the DML operation of the expansion table. The SQL statement is as follows:

Create TRIGGER or replace Tab_b_tri
After update on tab_b
For each row
Begin
Insert into tab_a (COL_A1) VALUES (: NEW.COL_B1); here: NEW.COL_B1 is the updated column value of the update operation
--Here you need to be aware of the different triggering types whose special variables: the difference between new and: Old saved values.
--commit; Note that with scenario two, a commit operation cannot be shown here, and trigger cannot perform a display submission without declaring a custom transaction management.
End Tab_b_tri ;