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“.

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:

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