(GO) Oracle triggers use: simple use of after insert with before insert note

This article was reproduced from: http://blog.csdn.net/kuangfengbuyi/article/details/41446125

When you create a trigger, the trigger type is after insert,

In the Begin

Select Fielda to v_a from TableA;

When executing to this sentence, an error occurs:

--pop-up error message hint

--ora-04091: Table tr_table has changed trigger/function cannot read it

--ORA-06512: at Iu_table Line 2

--ora-04088: Trigger iu_table Error during execution

Problem Analysis:

When executing DML statements in Oracle, it is necessary to display the commit operation. When we insert, triggers are triggered to perform various actions on the trigger table and the extension table, but at this point the trigger and the insert are in the same transaction management, so we cannot do any additional action on the trigger table if the INSERT statement is not committed. If other additional actions are performed, the exception information is thrown as above.

Solution:

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

Declare

pragma autonomous_transaction;

Begin

-------

--you need to show the commit transaction here

Commit;

End;

At the same time, the trigger cannot update the row where the trigger condition resides.

For example:

Create or replace Trigger tri_a

After insert on TableA

For each row

Declare

pragma autonomous_transaction;

Begin

Update TableA set fielda=1 where id=:new.id;

End

When you insert the data, you will find that the UPDATE statement is executed but does not take effect.

Then want to change: new.fielda= 1, after execution, error:

ORA-04084: Cannot change the new value of this trigger type

At this time, to change the strategy, but also according to individual needs,

Just what I need. You can update the trigger type after insert to before insert

(GO) Oracle triggers use: simple use of after insert with before insert note