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
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.
Thank You 🙂
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.