New Features of Oracle 11g-Compound Triggers)

We have previously introduced the virtual columns and read-only tables in the new Oracle 11g feature. Today we will introduce another new feature, the combined trigger.

In earlier versions of Oracle, you can use the FOLLOWS keyword to control the trigger execution sequence. For example:

 

01	CREATE OR REPLACE TRIGGER XXX_TRG

02	BEFORE INSERT

03	ON XXX_TABLE_NAME

04	REFERENCING NEW AS NEW OLD AS OLD

05	FOR EACH ROW

06	FOLLOWS YYY_TRG

07

DECLARE

08

BEGIN

09

NULL;

10

END;

Oracle 11g supports the new feature named composite trigger, which is similar to FOLLOWS but in different ways.

Trigger timing

Before learning about a combination trigger, we should first write a simple timing trigger.

A scheduled trigger is the time when the trigger will be executed, such as before statement, after statement, before each row, and after each row.

You can perform all the trigger actions in a combined trigger.

Trigger combination Guide

A-combined trigger combines all timer trigger
B-the combined trigger can only perform DML operations, but does not support DDL and system operations.
C-you can only use: OLD and: NEW variable Identifiers (before each row, after each row) in ROW-level code blocks)
D-does not support PRAGMA_AUTONOMOUS_TRANSACTION
E-you can use the WHEN statement to improve the trigger performance, but the combined trigger is not supported.
F-all scheduled triggers share the variables in a declaration until the transaction ends.
G-allows repeated execution of a scheduled trigger in a combination trigger
The h-INSERTING, UPDATING, and DELETING predicates are still valid in the combined trigger.
I-fixed variant table error Resolve mutating table error (ORA-04091)

Combined trigger Syntax:

 

01	CREATE OR REPLACE TRIGGER XXX_TRG

02	FOR INSERT OR UPDATE OR DELETE ON XXX_TABLE_NAME

03	COMPOUND TRIGGER

04

05

DECLARE

06	--DECLARATION SECTION FOR ALL TRIGGER TIMING USED

07

BEGIN

08	BEFORE STATEMENT IS

09

NULL;

10

11	AFTER STATEMENT IS

12

NULL;

13

14	BEFORE EACH ROW IS

15

NULL;

16

17	AFTER EACH ROW IS

18

NULL;

19

20	INSTEAD OF ROW IS

21

NULL;

22

23

END;

Note that before statement, after statement, before each row, and after each row are optional. You can use them as needed.

Instead of row is only used for database views.

If you view USER_TRIGGERS In the numeric dictionary view, you will find some new columns (BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW), which are related to the combination trigger.

Conclusion

Benefits of combined triggers:

1-implement transactions (?) in one place (?)
2-all triggers can share the claim segment
3-Maintenance Unit

4-fixed variant table error (ORA-04091)