Sunday, July 17, 2011

Execution Order of Triggers

You can create more than one trigger for the same timing point, at the same time we are not sure which trigge will execute first. To over come this confusion, Oracle 11g has provide the update trigger syntax, which includes FOLLOWS clause. The FOLLOWs clause guarantees the execution order of thye triggers created for the same timing point.

Example:


CREATE TABLE trigger_Execution_Order (
id NUMBER,
description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_Execution_Order_trg_1
BEFORE INSERT ON trigger_Execution_Order
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(trigger_Execution_Order_TRG_1 - Executed');
END;
/

CREATE OR REPLACE TRIGGER trigger_Execution_Order_trg_2
BEFORE INSERT ON trigger_Execution_Order
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line('trigger_Execution_Order_TRG_2 - Executed');
END;
/

Now, if we insert a record into the trigger_Execution_Order

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_Execution_Order VALUES (1, 'First Trigger will execute');
TRIGGER_EXECUTION_ORDER_TRG_1 - Executed
TRIGGER_EXECUTION_ORDER_TRG_2 - Executed

1 row created.

SQL>

By using the FOLLOWS clause, we can specify that the TRIGGER_EXECUTION_ORDER_TRG_2 trigger should be executed before the TRIGGER_EXECUTION_ORDER_TRG_1 trigger by recreating the TRIGGER_EXECUTION_ORDER_TRG_1 trigger.

CREATE OR REPLACE TRIGGER trigger_Execution_Order_trg_1
BEFORE INSERT ON trigger_Execution_Order
FOR EACH ROW
FOLLOWS trigger_Execution_Order_trg_2
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_EXECUTION_ORDER_TRG_1 - Executed');
END;
/

Now the trigger TRIGGER_EXECUTION_ORDER_TRG_1 will always follows the trigger TRIGGER_EXECUTION_ORDER_TRG_2.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_Execution_Order VALUES (2, 'Second Trigger will execute');
TRIGGER_EXECUTION_ORDER_TRG_2 - Executed
TRIGGER_EXECUTION_ORDER_TRG_1 - Executed

1 row created.

SQL>

No comments:

Post a Comment