New Features of Oracle 11g triggers

Trigger execution sequence

Combined trigger

Enable and disable triggers

Trigger execution sequence

Oracle allows more than one trigger to establish the same time point, but it never guarantees their execution order. The Oracle 11g trigger syntax now includes the FOLLOWS clause to ensure that the trigger execution sequence is at the same time point. The following example creates a table and two triggers triggered at the same time.

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 ('triggers _ 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 ('triggers _ FOLLOWS_TEST_TRG_2-executed ');

END;

/

We inserted the test table, and the trigger execution sequence was not guaranteed.

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 trigger TRIGGER_FOLLOWS_TEST_TRG_2 to execute TRIGGER_FOLLOWS_TEST_TRG_1 before TRIGGER_FOLLOWS_TEST_TRG_1 by recreating trigger_follows_follows_test_trg_1 using the Tri.

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 ('triggers _ FOLLOWS_TEST_TRG_1-executed ');

END;

/

 

Now TRIGGER_FOLLOWS_TEST_TRG_1 is always executed after TRIGGER_FOLLOWS_TEST_TRG_2.

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>

 

Delete the test table.

Drop table trigger_follows_test;

 

Combined trigger

A combination trigger allows code to be combined into a specific object at one or more time points. A separate time point can share a global declaration part, which is maintained during the trial period, once the statement ends, the trigger status is cleared no matter whether the execution is successful or incorrect. In earlier versions, this type of function can only be defined in a single package by defining multiple triggers and global variables.

The action definition of the combined TRIGGER is the same as that of other DML triggers, and the compound trigger clause is added. The subject of a trigger consists of an optional global declaration part and one or more timing points, each of which may contain a local declaration that cannot be 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>;

/

1 2 3 Next Page