Compound triggers and new options in 11G

There are 3 new options in 11g:

  • compound triggers
  • order of execution triggers
  • enable/disable at creation time

Compound triggers

Single trigger on a table/view that allows specify all timing points of the trigger
  • before statement
  • before each row
  • after each row
  • after statement
Each timing point can access common PL/SQL state(global declarations) of the trigger
You can easy avoid mutating table error
CREATE OR REPLACE TRIGGER schema.trigger_name
FOR dml_event_clause ON schema.TABLE
COMPOUND TRIGGER
 
-- Here you can define global variables, subprograms
-- that are visible in each timing points
 g_var DATE;
 
 PROCEDURE g_proc IS
 BEGIN
   g_var := SYSDATE;
 END; 
 
 BEFORE STATEMENT IS
 BEGIN
   NULL;
 END BEFORE STATEMENT;
 
 BEFORE EACH ROW IS
 BEGIN
   NULL;
 END BEFORE EACH ROW;
 
 AFTER EACH ROW IS
 BEGIN
   NULL;
 END AFTER EACH ROW;
 
 AFTER STATEMENT IS
 BEGIN
   NULL;
 END AFTER STATEMENT;
 
END;
/

Order of execution triggers

In Oracle you could always create many triggers with the same timing points (before statement | before each row | after each row | after statement) and dml event clause(insert | update | delete).

In such case Oracle never guarantees which trigger is executed first.

DROP TABLE test_tbl;
 
CREATE TABLE test_tbl
(id NUMBER);
 
CREATE OR REPLACE TRIGGER test_tbl_1_trg
before INSERT ON test_tbl
FOR each ROW
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_1_trg');
END;
/
 
CREATE OR REPLACE TRIGGER test_tbl_2_trg
before INSERT ON test_tbl
FOR each ROW
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_2_trg');
END;
/
 
CREATE OR REPLACE TRIGGER test_tbl_3_trg
before INSERT ON test_tbl
FOR each ROW
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_3_trg');
END;
/
 
SET serveroutput ON
INSERT INTO test_tbl VALUES(5);
 
1 rows inserted.
test_tbl_3_trg
test_tbl_2_trg
test_tbl_1_trg

In 11g it’s possible to do it by using explicit defined clause FOLLOWS.

DROP TABLE test_tbl;
 
CREATE TABLE test_tbl
(id NUMBER);
 
CREATE OR REPLACE TRIGGER test_tbl_1_trg
before INSERT ON test_tbl
FOR each ROW
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_1_trg');
END;
/
 
CREATE OR REPLACE TRIGGER test_tbl_3_trg
before INSERT ON test_tbl
FOR each ROW
follows test_tbl_1_trg --NEW CLAUSE FOLLOWS
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_3_trg');
END;
/
 
CREATE OR REPLACE TRIGGER test_tbl_2_trg
before INSERT ON test_tbl
FOR each ROW
follows test_tbl_3_trg --NEW CLAUSE FOLLOWS
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_2_trg');
END;
/
 
SET serveroutput ON
INSERT INTO test_tbl VALUES(5);
 
1 rows inserted.
test_tbl_1_trg
test_tbl_3_trg
test_tbl_2_trg

 Enable/disable during creation time

In previous releases you could do this

  • ALTER TRIGGER <trigger-name> DISABLE;
  • ALTER TRIGGER <trigger-name> ENABLE;
  • ALTER TABLE <table-name> DISABLE ALL TRIGGERS;
  • ALTER TABLE <table-name> ENABLE ALL TRIGGERS;

Now you can disable trigger during creation (default is enable)

CREATE TABLE test_tbl
(id NUMBER);
 
CREATE OR REPLACE TRIGGER test_tbl_1_trg
before INSERT ON test_tbl
FOR each ROW
disable  --NEW FEATURE IS HERE
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_1_trg');
END;
/
 
CREATE OR REPLACE TRIGGER test_tbl_2_trg
before INSERT ON test_tbl
FOR each ROW
enable
BEGIN
  DBMS_OUTPUT.put_line('test_tbl_2_trg');
END;
/
 
SELECT trigger_name, status FROM user_triggers 
WHERE table_name='TEST_TBL';
 
TRIGGER_NAME                   STATUS 
------------------------------ --------
TEST_TBL_1_TRG                 DISABLED 
TEST_TBL_2_TRG                 ENABLED 
 
SET serveroutput ON
INSERT INTO test_tbl VALUES(5);
 
1 rows inserted.
test_tbl_2_trg

NOTE – if you created trigger with ENABLE whose PLSQL body has got errors your DML operations will fail. Creating trigger with DISABLE will prevent such errors. It can be important on production systems. First create triggers with DISABLE clause and when you sure it’s compiled without errors ENABLE it later.

Have a fun 🙂

Tomasz

 

 

3 thoughts on “Compound triggers and new options in 11G

  1. Hi,

    New features summary is too good. Clearly explains each and every feature. Great work..

    Keep it and up

    Thank you very much for the great work.

  2. Great explanations. I didn’t try these new features but it looks like Oracle now is able to solve their problem with mutating. Before an application had to be redesign to avoid such problem.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.