In real world, as you create more and more triggers containing more and more business logic, it becomes difficult to recall which triggers handle which rules and how all of the triggers interact. Wouldn’t it be nice to be able to put row and statement triggers together in the same code object as well ?
Starting with Oracle Database 11g, you can use the COMPOUND trigger to do just that.
Compound triggers look a lot like PL/SQL packages. All of the related code and logic is in one place, making it easy to debug and modify.
BEFORE STATEMENT ::: The code in this section will fire before a DML statement executes, just like a standalone BEFORE statement trigger does.
BEFORE EACH ROW ::: The code in this section gets executed before each and every row is processed by the DML statement.
AFTER EACH ROW ::: The code in this section gets executed after each and every row is processed by the DML statement.
AFTER STATEMENT ::: The code in this section will fire after a DML statement executes, just like a standalone AFTER statement trigger does.
The rules for standalone triggers apply to compound triggers as well—for example, record values (OLD and NEW) cannot be modified in statement-level triggers.
SQL> CREATE TABLE incremented_values
2 (
3 value_inserted NUMBER,
4 value_incremented NUMBER
5 );
Table created.SQL> CREATE OR REPLACE TRIGGER compounder FOR
2 UPDATE OR INSERT OR DELETE
3 ON incremented_values
4 COMPOUND TRIGGER
5 v_global_var NUMBER := 1;
6 BEFORE STATEMENT
7 IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE ('Compound:BEFORE S:' || v_global_var);
10 v_global_var := v_global_var + 1;
11 END
12 BEFORE STATEMENT;
13
14 BEFORE EACH ROW
15 IS
16 BEGIN
17 DBMS_OUTPUT.PUT_LINE ('Compound:BEFORE R:' || v_global_var);
18 v_global_var := v_global_var + 1;
19 END
20 BEFORE EACH ROW;
21
22 AFTER EACH ROW
23 IS
24 BEGIN
25 DBMS_OUTPUT.PUT_LINE ('Compound:AFTER R:' || v_global_var);
26 v_global_var := v_global_var + 1;
27 END
28 AFTER EACH ROW;
29
30 AFTER STATEMENT
31 IS
32 BEGIN
33 DBMS_OUTPUT.PUT_LINE ('Compound:AFTER S:' || v_global_var);
34 v_global_var := v_global_var + 1;
35 END
36 AFTER STATEMENT;
37
38 END;
39 /
Trigger created.
SQL> SELECT * FROM incremented_values;
no rows selected
SQL> BEGIN
2 INSERT INTO incremented_values VALUES(1,1);
3 INSERT INTO incremented_values VALUES(2,2);
4 END;
5 /
--OUTPUT :::
Compound:BEFORE S:1
Compound:BEFORE R:2
Compound:AFTER R:3
Compound:AFTER S:4
Compound:BEFORE S:1
Compound:BEFORE R:2
Compound:AFTER R:3
Compound:AFTER S:4
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM incremented_values;
VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
1 1
2 2
SQL> INSERT INTO incremented_values VALUES(3,3);
Compound:BEFORE S:1
Compound:BEFORE R:2
Compound:AFTER R:3
Compound:AFTER S:4
1 row created.
SQL> INSERT INTO incremented_values VALUES(4,4);
Compound:BEFORE S:1
Compound:BEFORE R:2
Compound:AFTER R:3
Compound:AFTER S:4
1 row created.
SQL> SELECT * FROM incremented_values;
VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
1 1
2 2
3 3
4 4
SQL> COMMIT;
Commit complete.
--Notice that the output of the global variable was set back to 1 when the second statement executed. That's because the scope of the compound trigger is the DML statement that fires it. Once that statement completes, the compound trigger and its in-memory values start anew. That simplifies the logic.SQL> ALTER TABLE incremented_values ADD CONSTRAINT a_pk PRIMARY KEY ( value_inserted );
Table altered.
SQL> INSERT INTO incremented_values VALUES(1,1);
Compound:BEFORE S:1
Compound:BEFORE R:2
INSERT INTO incremented_values VALUES(1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.A_PK) violated
--The next INSERT also throws the primary key error again, as expected. But that is not what’s exceptional about this situation—what’s exceptional is that the global variable was reinitialized back to 1 without any extra code having to be written. The firing DML completed, so the compound trigger fell out of scope and everything started anew for the next statement.
SQL> INSERT INTO incremented_values VALUES(1,1);
Compound:BEFORE S:1
Compound:BEFORE R:2
INSERT INTO incremented_values VALUES(1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.A_PK) violated
SQL> CREATE OR REPLACE TRIGGER compounder FOR
2 UPDATE OR INSERT OR DELETE
3 ON incremented_values
4 COMPOUND TRIGGER
5 BEFORE STATEMENT
6 IS
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE ('Compound:BEFORE S:');
9 END BEFORE STATEMENT;
10
11 BEFORE EACH ROW
12 IS
13 BEGIN
14 DBMS_OUTPUT.PUT_LINE ('Compound:BEFORE R:');
15 END BEFORE EACH ROW;
16
17 AFTER EACH ROW
18 IS
19 BEGIN
20 DBMS_OUTPUT.PUT_LINE ('Compound:AFTER R:');
21 END AFTER EACH ROW;
22
23 AFTER STATEMENT
24 IS
25 BEGIN
26 DBMS_OUTPUT.PUT_LINE ('Compound:AFTER S:');
27 END AFTER STATEMENT;
28 END;
29 /
Trigger created.
SQL> INSERT INTO incremented_values VALUES(1,1);
Compound:BEFORE S:
Compound:BEFORE R:
Compound:AFTER R:
Compound:AFTER S:
1 row created.
SQL> BEGIN
2 INSERT INTO incremented_values VALUES(2,2);
3 INSERT INTO incremented_values VALUES(3,3);
4 END;
5 /
--OUTPUT :::
Compound:BEFORE S:
Compound:BEFORE R:
Compound:AFTER R:
Compound:AFTER S:
Compound:BEFORE S:
Compound:BEFORE R:
Compound:AFTER R:
Compound:AFTER S:
SQL> INSERT ALL
2 INTO incremented_values (value_inserted,value_incremented) VALUES(4,4)
3 INTO incremented_values (value_inserted,value_incremented) VALUES(5,5)
4 INTO incremented_values (value_inserted,value_incremented) VALUES(6,6)
5 SELECT * FROM dual;
Compound:BEFORE S:
Compound:BEFORE S:
Compound:BEFORE S:
Compound:BEFORE R:
Compound:BEFORE R:
Compound:BEFORE R:
Compound:AFTER R:
Compound:AFTER R:
Compound:AFTER R:
Compound:AFTER S:
Compound:AFTER S:
Compound:AFTER S:
3 rows created.
SQL> SELECT * FROM incremented_values;
VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
1 1
2 2
3 3
4 4
5 5
6 6
6 rows selected.Compound triggers also can be used with the FOLLOWS syntax.
SQL> CREATE OR REPLACE TRIGGER follows_compounder
2 BEFORE INSERT
3 ON incremented_values
4 FOR EACH ROW
5 FOLLOWS compounder
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('Following Trigger');
8 END;
9 /
Trigger created.
SQL> INSERT INTO incremented_values VALUES(5,5);
Compound:BEFORE S:1
Compound:BEFORE R:2
Following Trigger
Compound:AFTER R:3
Compound:AFTER S:4
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM incremented_values;
VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
1 1
2 2
3 3
4 4
5 5
--If a standalone trigger is defined to follow a compound trigger that does not contain a trigger to fire on the same statement or row, then the FOLLOWS clause is simply ignored.
