Tuesday, 6 January 2015

Trigger Enhancements in Oracle Database 11g Release 1

·         Execution Order of Triggers
·         Compound Triggers
·         Enable and Disable Triggers
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