8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Trigger Enhancements in Oracle Database 11g Release 1
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;
For more information see:
- What's New in PL/SQL? - Oracle Database PL/SQL Language Reference 11g Release 1 (11.1)
- CREATE TRIGGER
- Compound Triggers
Hope this helps. Regards Tim...