Oracle Tips #4 [Trigger : Mutating Tables & Autonomos Transactions]
Oracle Version : 10g
As you know inside a Oracle row trigger you cannot access to the base table, for example when you make a trigger like this, on a table named “activity“.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR REPLACE TRIGGER UNAME.ACTIVITY_FK_MENU_INTEGRITY AFTER INSERT OR UPDATE ON UNAME.ACTIVITY REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE tmpVar NUMBER; e_Menu_Already_Exists EXCEPTION; BEGIN Select count(*) into tmpVar from Activity aa where (aa.fk_menu = :new.fk_menu)and(aa.pk_id <> :new.pk_id); -- if (tmpVar > 0) then --:New.FK_MENU := null; -- raise e_Menu_Already_Exists; -- end if; END ACTIVITY_FK_MENU_INTEGRITY; |
when you try to use the table making an update happen this :
To avoid this error and obviusly assuming the risk about ricorsive trigger, you can define a custom transaction for your trigger, so you can access to data inside the mutating table. To do this you need to declare “PRAGMA AUTONOMOUS_TRANSACTION;” means that the trigger give a specific transaction. So now you need to specify an explicit “commit;” at the end of the trigger.
The example will result like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR REPLACE TRIGGER UNAME.ACTIVITY_FK_MENU_INTEGRITY AFTER INSERT OR UPDATE ON UNAME.ACTIVITY REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; tmpVar NUMBER; e_Menu_Already_Exists EXCEPTION; BEGIN Select count(*) into tmpVar from Activity aa where (aa.fk_menu = :new.fk_menu)and(aa.pk_id <> :new.pk_id); commit; END ACTIVITY_FK_MENU_INTEGRITY; |
that’s all until next tip
bye
ivan
