Execution Order of Triggers
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 FOLLOWS clause.
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;
Compound Triggers
A compound trigger allows code for one or more timing points
for a specific object to be combined into a single trigger. The individual
timing points can share a single global declaration section, whose state is
maintained for the lifetime of the statement. Once a statement ends, due to
successful completion or an error, the trigger state is cleaned up. In previous
releases this type of functionality was only possible by defining multiple
triggers whose code and global variables were defined in a separate package, as
shown in the Mutating Table Exceptions article, but
the compound trigger allows for a much tidier solution.
The triggering actions are defined in the same way as any
other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main
body of the trigger is made up of an optional global declaration section and
one or more timing point sections, each of which may contain a local
declaration section whose state is not maintained.
CREATE OR REPLACE TRIGGER <trigger-name>
FOR <trigger-action> ON <table-name>
COMPOUND TRIGGER
-- Global declaration.
g_global_variable VARCHAR2(10);
BEFORE STATEMENT IS
BEGIN
NULL; -- Do something here.
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
NULL; -- Do something here.
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
NULL; -- Do something here.
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
NULL; -- Do something here.
END AFTER STATEMENT;
END <trigger-name>;
/
The following code creates a test table and a compound
trigger that fires for each timing point associated with insert, update and
delete statements. The triggering actions are logged in a PL/SQL table defined
in the global declaration section. The final timing point for each statement
prints out the content of the PL/SQL table to show that the variable state has
been maintained throughout the lifetime of the statement.
CREATE TABLE compound_trigger_test (
id NUMBER,
description VARCHAR2(50)
);
CREATE OR REPLACE TRIGGER compound_trigger_test_trg
FOR INSERT OR UPDATE OR DELETE ON compound_trigger_test
COMPOUND TRIGGER
-- Global declaration.
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
BEFORE STATEMENT IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last) := 'BEFORE STATEMENT - INSERT';
WHEN UPDATING THEN
l_tab(l_tab.last) := 'BEFORE STATEMENT - UPDATE';
WHEN DELETING THEN
l_tab(l_tab.last) := 'BEFORE STATEMENT - DELETE';
END CASE;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last) := 'BEFORE EACH ROW - INSERT (new.id=' || :new.id || ')';
WHEN UPDATING THEN
l_tab(l_tab.last) := 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
WHEN DELETING THEN
l_tab(l_tab.last) := 'BEFORE EACH ROW - DELETE (old.id=' || :old.id || ')';
END CASE;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id || ')';
WHEN UPDATING THEN
l_tab(l_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
WHEN DELETING THEN
l_tab(l_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id || ')';
END CASE;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last) := 'AFTER STATEMENT - INSERT';
WHEN UPDATING THEN
l_tab(l_tab.last) := 'AFTER STATEMENT - UPDATE';
WHEN DELETING THEN
l_tab(l_tab.last) := 'AFTER STATEMENT - DELETE';
END CASE;
FOR i IN l_tab.first .. l_tab.last LOOP
DBMS_OUTPUT.put_line(l_tab(i));
END LOOP;
l_tab.delete;
END AFTER STATEMENT;
END compound_trigger_test_trg;
/
By issuing several insert, update and delete statements
against the test table we can see that the compound trigger is working as
expected.
SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO compound_trigger_test VALUES (1, 'ONE');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)
AFTER STATEMENT - INSERT
1 row created.
SQL> INSERT INTO compound_trigger_test VALUES (2, 'TWO');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=2)
AFTER EACH ROW - INSERT (new.id=2)
AFTER STATEMENT - INSERT
1 row created.
SQL> UPDATE compound_trigger_test SET id = id;
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER EACH ROW - UPDATE (new.id=2 old.id=2)
BEFORE EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER STATEMENT - UPDATE
2 rows updated.
SQL> DELETE FROM compound_trigger_test;
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=2)
AFTER EACH ROW - DELETE (old.id=2)
BEFORE EACH ROW - DELETE (old.id=1)
AFTER EACH ROW - DELETE (old.id=1)
AFTER STATEMENT - DELETE
2 rows deleted.
SQL>
Don't forget
to clean up the test table. DROP TABLE compound_trigger_test;
For a more practical use of compound triggers, we can take
the example quoted in the Mutating Table Exceptions article and
replace the two triggers and the package with a single compound trigger, as
shown below.
CREATE OR REPLACE TRIGGER tab1_compound_trigger
FOR INSERT OR UPDATE ON tab1
COMPOUND TRIGGER
TYPE t_change_tab IS TABLE OF tab1_audit%ROWTYPE;
g_change_tab t_change_tab := t_change_tab();
AFTER EACH ROW IS
BEGIN
g_change_tab.extend;
g_change_tab(g_change_tab.last).id := tab1_audit_seq.NEXTVAL;
IF INSERTING THEN
g_change_tab(g_change_tab.last).action := 'INSERT';
ELSE
g_change_tab(g_change_tab.last).action := 'UPDATE';
END IF;
g_change_tab(g_change_tab.last).tab1_id := :new.id;
g_change_tab(g_change_tab.last).created_time := SYSTIMESTAMP;
END AFTER EACH ROW;
AFTER STATEMENT IS
l_count NUMBER(10);
BEGIN
FOR i IN g_change_tab.first .. g_change_tab.last LOOP
SELECT COUNT(*)
INTO g_change_tab(i).record_count
FROM tab1;
END LOOP;
FORALL i IN g_change_tab.first .. g_change_tab.last
INSERT INTO tab1_audit VALUES g_change_tab(i);
g_change_tab.delete;
END AFTER STATEMENT;
END tab1_compound_trigger;
/
From a timing point perspective, the Compound Trigger Restrictions follow
very closely with those of individual statement and row level triggers. The
main point of interest here is the control of execution order. If multiple
compound triggers are defined for the same object, their order of execution can
be controlled using the FOLLOWS clause, but this cannot be used to control
execution order when both compound and regular DML triggers are defined against
a single object. In such situations it is better to stick with all DML
triggers, or all compound triggers.
Enable and Disable Triggers
It has been possible to enable and disable triggers for some
time using the ALTER TRIGGER and ALTER TABLE commands.
·
ALTER TRIGGER <trigger-name>
DISABLE;
·
ALTER TRIGGER <trigger-name>
ENABLE;
·
ALTER TABLE <table-name> DISABLE
ALL TRIGGERS;
·
ALTER TABLE <table-name> ENABLE
ALL TRIGGERS;
Prior to 11g, it was only possible to create triggers in the
enabled state, then subsequently disable them. Now they can be explicitly
enabled or disabled at creation time, with the enabled state as the default.
CREATE TABLE trigger_control_test (
id NUMBER,
description VARCHAR2(50)
);
CREATE OR REPLACE TRIGGER trigger_control_test_trg
BEFORE INSERT ON trigger_control_test
FOR EACH ROW
ENABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed');
END;
/
SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_control_test VALUES (1, 'ONE');
TRIGGER_CONTROL_TEST_TRG - Executed
1 row created.
SQL>
CREATE OR REPLACE TRIGGER trigger_control_test_trg
BEFORE INSERT ON trigger_control_test
FOR EACH ROW
DISABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed');
END;
/
SQL> INSERT INTO trigger_control_test VALUES (2, 'TWO');
1 row created.
SQL>
Don't forget to clean up the test table.DROP TABLE trigger_control_test;
No comments:
Post a Comment