Senthil Murugan's Blog

Home » Oracle » Execution Order of Triggers–11g Features

Execution Order of Triggers–11g Features

Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point. The following example creates a table with two triggers for the same timing point.

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

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_1 – Executed’);
END;
/

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_2 – Executed’);
END;
/

If we insert into the test table, there is no guarantee of the execution order.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (1, ‘ONE’);
TRIGGER_FOLLOWS_TEST_TRG_1 – Executed
TRIGGER_FOLLOWS_TEST_TRG_2 – Executed

1 row created.

SQL>

We can specify that the TRIGGER_FOLLOWS_TEST_TRG_2 trigger should be executed before the TRIGGER_FOLLOWS_TEST_TRG_1 trigger by recreating the TRIGGER_FOLLOWS_TEST_TRG_1 trigger using the FOLLOWSclause.

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
BEGIN
  DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_1 – Executed’);
END;
/

Now the TRIGGER_FOLLOWS_TEST_TRG_1 trigger always follows the TRIGGER_FOLLOWS_TEST_TRG_2 trigger.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (2, ‘TWO’);
TRIGGER_FOLLOWS_TEST_TRG_2 – Executed
TRIGGER_FOLLOWS_TEST_TRG_1 – Executed

1 row created.

SQL>

Don’t forget to clean up the test table.

DROP TABLE trigger_follows_test;

 

Thanks to Trigger Enhancements in Oracle 11g

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: