Trigger Enhancements in
Oracle Database 11g Release 1
Execution Order of Triggers
Oracle
allows more than one trigger to be created for the same timing point, but it
has never guaranteed the execution order of those triggers. The Oracle 11g
trigger syntax now includes the FOLLOWS clause to guarantee execution order for
triggers defined with the same timing point. The following example creates a
table with two triggers for the same timing point.
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('TRIGGER_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('TRIGGER_FOLLOWS_TEST_TRG_2
- Executed');
END;
/
If
we insert into the test table, there is no guarantee of the execution order.
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 that the TRIGGER_FOLLOWS_TEST_TRG_2 trigger should be executed before the TRIGGER_FOLLOWS_TEST_TRG_1 trigger by recreating the TRIGGER_FOLLOWS_TEST_TRG_1 trigger using the FOLLOWS clause.
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('TRIGGER_FOLLOWS_TEST_TRG_1
- Executed');
END;
/
Now
the TRIGGER_FOLLOWS_TEST_TRG_1 trigger always follows the TRIGGER_FOLLOWS_TEST_TRG_2 trigger.
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>
Don't
forget to clean up the test table.
DROP
TABLE trigger_follows_test;
In database terminology, triggers are the objects which demonstrate the logic of schedule and automation.
Triggers are automatically invoked at a defined event and timing without any explicit calls. The logic embedded within the trigger and invocation must be well directed and synchronized to maintain database purity.
Oracle 11g made remarkable enhancements in Database Triggers. These enhancements and additions have transformed triggers into a logical, stable, and comprehensive database platform. In the past versions, developers used to face issues under certain conditions as listed below.
- Multiple triggers
for single timing
- Mutating table
confrontation
- Control the
trigger execution by enabling and disabling it
Introduction of compound triggers which triggers different logic at different timings, guaranteed execution sequence of triggers and enable/disable functionalities make the PL/SQL a more efficient language and require less coding.
The article projects the transformations undergone by database triggers in 11g release of Oracle.
Trigger Enhancements
Setting the Trigger order
Earlier, if a DML event demands multiple triggering actions without altering the existing code, developers used to create multiple triggers for same timing and event. For example, a table EMPLOYEE can have two AFTER UPDATE OF EMPLOYEE FOR EACH ROW triggers.
Such activity was possible since Oracle 8i, but usually the trigger timing determines the order of its execution but the aforesaid baffling situation gives the complete privilege to the database server to determine the execution order of the triggers i.e. random execution. It remained a ‘crossed fingers’ situation for the developers to predict the execution order. Unsorted execution of DML triggers can produce unexpected results where setting or initializations of parameters are involved.
Now, oracle has released the flexibility to set the sequence of execution of triggers for the same triggering event and timing. Two new keywords FOLLOWS and PRECEDES have been introduced to force the triggers to follow set execution order.
Example Syntax [1]
CREATE OR REPLACE TRIGGER
[TRIGGER TO FOLLOW]
[TIMING SPECIFICATION]
[EVENT SPECIFICATION]
FOLLOWS [TRIGGER TO BE FOLLOWED]
[TIMING SPECIFICATION]
[EVENT SPECIFICATION]
FOLLOWS [TRIGGER TO BE FOLLOWED]
Example Code [1]: Demonstrate the use of FOLLOWS keyword
CREATE OR REPLACE TRIGGER
T_UPD_SAL
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Executing T_UD_SAL’);
INSERT INTO EMP_SAL_ARCHIVE
VALUES (EMPLOYEE_ID, DEPARTMENT, SALARY, HIKE_PERC, HIKE_DATE)
(:OLD.EMPLOYEE_ID,
:OLD.DEPARTMENT_ID,
:NEW.SALARY,
((:NEW.SALARY-:OLD.SALARY)/:OLD.SALARY)*100,
SYSDATE)
END;
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Executing T_UD_SAL’);
INSERT INTO EMP_SAL_ARCHIVE
VALUES (EMPLOYEE_ID, DEPARTMENT, SALARY, HIKE_PERC, HIKE_DATE)
(:OLD.EMPLOYEE_ID,
:OLD.DEPARTMENT_ID,
:NEW.SALARY,
((:NEW.SALARY-:OLD.SALARY)/:OLD.SALARY)*100,
SYSDATE)
END;
CREATE OR REPLACE TRIGGER
T_UPD_JOB
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
FOLLOWS T_UPD_SAL
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Executing T_UD_JOB’);
INSERT INTO EMP_JOB_ARCHIVE
VALUES(EMPLOYEE_ID, DEPARTMENT, OLD_JOB, NEW_JOB, PROM_DATE)
(:OLD.EMPLOYEE_ID,
:OLD.DEPARTMENT_ID,
:OLD.JOB_ID,
:NEW.JOB_ID,
SYSDATE);
END;
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
FOLLOWS T_UPD_SAL
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Executing T_UD_JOB’);
INSERT INTO EMP_JOB_ARCHIVE
VALUES(EMPLOYEE_ID, DEPARTMENT, OLD_JOB, NEW_JOB, PROM_DATE)
(:OLD.EMPLOYEE_ID,
:OLD.DEPARTMENT_ID,
:OLD.JOB_ID,
:NEW.JOB_ID,
SYSDATE);
END;
Now, the triggers would be executed in the set order. This ensures the logical and sorted execution of modular logic in the application.
DISABLED Triggers
Prior to Oracle 11g, by default all triggers used to get created in ENABLED state. In real time production systems, this behavior had rooted out reluctant and unavoidable issues. Suppose the database support team applies a production patch, which contains a new trigger script. Upon execution, the trigger script raises error due to some missing reference. If somehow the trigger could have been only created with compilation errors, but not on live association with the event [table], the error could be resolved out.
Oracle 11g resolves such scenarios by allowing a trigger to be created in DISABLED state. Note that state is not at all new for triggers. Earlier too, a trigger can be disabled using ALTER TRIGGER command. Oracle 11g has widened this feature by taking it at creation level. A disabled trigger can be enabled at any point of time in the program.
Example Code [2]: Demonstration of DISABLED trigger
CREATE OR REPLACE TRIGGER
T_CRT_DIS
BEFORE INSERT ON ORDER
FOR EACH ROW
DISABLED
BEGIN
SELECT 1
INTO L_EXIST
FROM WAREHOUSE
WHERE ITEM_CODE = :NEW.PROD_CODE;
DBMS_OUTPUT.PUT_LINE(‘Product in stock: Go ahead’);
EXCEPTION
WHEN NO_DATA_FOUND THE RAISE_APPLICATION_ERROR(-20100, ‘Shortage of the product: No Order can be placed’);
BEFORE INSERT ON ORDER
FOR EACH ROW
DISABLED
BEGIN
SELECT 1
INTO L_EXIST
FROM WAREHOUSE
WHERE ITEM_CODE = :NEW.PROD_CODE;
DBMS_OUTPUT.PUT_LINE(‘Product in stock: Go ahead’);
EXCEPTION
WHEN NO_DATA_FOUND THE RAISE_APPLICATION_ERROR(-20100, ‘Shortage of the product: No Order can be placed’);
DML triggers are now faster
DML Triggers in Oracle 11g are comparatively faster than their counterparts in earlier versions. The performance can be clearly measured by running a trigger separately in 10g and 11g and recording their execution time. I have executed a trigger T_CHECK_ORDER in Oracle 10g and 11g separately. Their execution time upon operation is recorded as 153
Figuire shows the reduction in execution time of the DML triggers in Oracle 11g.
Compound Triggers
A compound trigger allows code for one or more timing points for a
specific object to be combined into a single trigger. The individual timing
points can share a single global declaration section, whose state is maintained
for the lifetime of the statement. Once a statement ends, due to successful
completion or an error, the trigger state is cleaned up. In previous releases
this type of functionality was only possible by defining multiple triggers
whose code and global variables were defined in a separate package, as shown in
the Mutating Table
Exceptions article, but the compound trigger allows for a much tidier
solution.
The triggering actions are defined in the same way as any other
DML trigger, with the addition of the
COMPOUND TRIGGER clause. The main body of the
trigger is made up of an optional global declaration section and one or more
timing point sections, each of which may contain a local declaration section
whose state is not maintained.New Language Features
Compound Triggers
Compound triggers are another new tool in the kit to support reduced coding and interactivity. It combines all four triggering events into a single piece of code. Besides the coding efficiency, it tackles some bigger issues in the picture.
- Mutating table
(ORA-04091) scenarios
- Suppose an EACH
ROW triggers do some transaction in some other table using new values.
Imagine the situation when dealing with millions of data and double transaction for each row through trigger action would cause huge losses to performance.
Earlier, workaround solutions do existed for the above problems, but with loads of complex coding using collections and multiple triggers. It used to become nightmare for developers to simulate and test such scenarios.
Compound triggers readily deal with all above scenarios in an efficient and interactive manner. The new trigger feature not only boosts up the performance during bulk operations, but also holds the state of its variables and member constructs till the execution of the statement. They are reset only at the beginning of new statement. Note that compound trigger is an optional feature. Separate DML triggers for different timing can still be created in earlier fashion.
On the lower side, compound triggers are only available for DML triggers. DDL and system level triggers still follow the old convention. In addition, condition based and autonomous compound triggers are not supported. An important point to mention here is the exception handling. It has to be handled explicitly in all the timing blocks, and not in the trigger body.
Syntax
The compound trigger syntax contains the man body and four blocks representing four timings associated with DML events. Note the COMPOUND TRIGGER keyword to differentiate with the other database triggers.
CREATE OR REPLACE TRIGGER
[TRIGGER NAME]
FOR [DML] ON [TABLE NAME]
COMPOUND TRIGGER
– Initial section
– Declarations
– Subprograms
—Optional section—
BEFORE STATEMENT IS
…;
—Optional section—
AFTER STATEMENT IS
…;
—Optional section—
BEFORE EACH ROW IS
…;
—Optional section—
AFTER EACH ROW IS
…;
END;
For database views
INSTEAD OF EACH ROW IS
…;
END;
FOR [DML] ON [TABLE NAME]
COMPOUND TRIGGER
– Initial section
– Declarations
– Subprograms
—Optional section—
BEFORE STATEMENT IS
…;
—Optional section—
AFTER STATEMENT IS
…;
—Optional section—
BEFORE EACH ROW IS
…;
—Optional section—
AFTER EACH ROW IS
…;
END;
For database views
INSTEAD OF EACH ROW IS
…;
END;
Usage
guidelines
1.
Each timing handler block must appear only once in the compound
trigger body
2.
All timing handler blocks are optional
3.
Compound Trigger metadata can be captured in USER_TRIGGERS view.
The new columns added are as below:
A new trigger type ‘COMPOUND’ would be updated for compound triggers. Based on availability of the timing block in the trigger body,
Illustrations of Compound Triggers
Compound trigger as performance savior
I shall site a scenario where concurrent loading into a table is achieved through Compound trigger. Bulk loading uses an associative array to hold the data to be loaded. The loading is done after the statement execution completes. Refer the trigger code below
Example Code [3]: Compound trigger to implement bulk loading
CREATE OR REPLACE TRIGGER
TRG_INS_ORDER
FOR INSERT ON ORDERS
COMPOUND TRIGGER
TYPE ORDER_T IS TABLE OF ORDER_ARCHIVE%ROWTYPE
INDEX BY PLS_INTEGER;
L_ORDERS ORDER_T;
I NUMBER := 0;
AFTER EACH ROW IS
BEGIN
I := I+1;
L_ORDERS(I).ORD_ID := :NEW.ORD_ID;
L_ORDERS(I).ORD_CODE := :NEW.ITEM_CODE;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Statement level loading’);
FORALL J IN 1..L_ORDERS.COUNT
INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
L_ORDERS.DELETE;
I := 0;
END AFTER STATEMENT;
END;
/
Trigger created.
FOR INSERT ON ORDERS
COMPOUND TRIGGER
TYPE ORDER_T IS TABLE OF ORDER_ARCHIVE%ROWTYPE
INDEX BY PLS_INTEGER;
L_ORDERS ORDER_T;
I NUMBER := 0;
AFTER EACH ROW IS
BEGIN
I := I+1;
L_ORDERS(I).ORD_ID := :NEW.ORD_ID;
L_ORDERS(I).ORD_CODE := :NEW.ITEM_CODE;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Statement level loading’);
FORALL J IN 1..L_ORDERS.COUNT
INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
L_ORDERS.DELETE;
I := 0;
END AFTER STATEMENT;
END;
/
Trigger created.
–Inserting test data into ORDERS table
INSERT INTO ORDERS(ORD_ID,
ITEM_CODE)
SELECT ORD_SEQ.NEXTVAL, ‘PROD ‘||LEVEL
FROM DUAL
CONNECT BY LEVEL < 1000;
Statement level loading
999 rows created.
SELECT ORD_SEQ.NEXTVAL, ‘PROD ‘||LEVEL
FROM DUAL
CONNECT BY LEVEL < 1000;
Statement level loading
999 rows created.
To be noticed, the simultaneous insertion process has been carried out in bulk. This pulls up the performance to a major extent. If the same insert operation have had used conventional row level trigger, it would have shrunk the performance by 1000 insert statements.
Resolving Table mutation scenarios
Mutating table scenarios have been hovering over the multi-user based applications for a long time. Several times, tedious workaround solutions and unreliability has forces the architects to modify the design, so as to avoid table mutation during parallel real time processing. Compound triggers in Oracle 11g provide concrete logic base to deal with such scenarios. The trigger example below shows how they handle and achieve such events.
Before moving to the solution, first I will simulate the table mutation scenario.
The row level trigger TRG_INS_ORDERS on ORDERS table will display the updated item code.
CREATE OR REPLACE TRIGGER
TRG_INS_ORDERS
AFTER UPDATE OF ITEM_CODE ON ORDERS
FOR EACH ROW
DECLARE
L_ITEM VARCHAR2(200);
BEGIN
SELECT ITEM_CODE INTO L_ITEM FROM ORDERS WHERE ORD_ID = :OLD.ORD_ID;
DBMS_OUTPUT.PUT_LINE(‘ITEM CODE CHANGED FROM ‘||:OLD.ITEM_CODE||’ TO ‘||L_ITEM);
* END;
SQL> /
Trigger created.
AFTER UPDATE OF ITEM_CODE ON ORDERS
FOR EACH ROW
DECLARE
L_ITEM VARCHAR2(200);
BEGIN
SELECT ITEM_CODE INTO L_ITEM FROM ORDERS WHERE ORD_ID = :OLD.ORD_ID;
DBMS_OUTPUT.PUT_LINE(‘ITEM CODE CHANGED FROM ‘||:OLD.ITEM_CODE||’ TO ‘||L_ITEM);
* END;
SQL> /
Trigger created.
UPDATE ORDERS
SET ITEM_CODE=’ITEM:’||ORD_ID
WHERE ORD_ID=10;
update orders
*
ERROR at line 1:
ORA-04091: table SCOTT.ORDERS is mutating, trigger/function may not see it
ORA-06512: at “SCOTT.TRG_INS_ORDERS “, line 4
ORA-04088: error during execution of trigger ‘SCOTT.TRG_INS_ORDERS’
SET ITEM_CODE=’ITEM:’||ORD_ID
WHERE ORD_ID=10;
update orders
*
ERROR at line 1:
ORA-04091: table SCOTT.ORDERS is mutating, trigger/function may not see it
ORA-06512: at “SCOTT.TRG_INS_ORDERS “, line 4
ORA-04088: error during execution of trigger ‘SCOTT.TRG_INS_ORDERS’
The table remains in the floating state, when the trigger TRG_INS_ORDERS tries to select and display the changed value. Such situations are well handled using compound triggers. Check the example below.
CREATE OR REPLACE TRIGGER
TRG_INS_ORDERS
FOR UPDATE ON ORDERS
COMPOUND TRIGGER
TYPE ITEM_CODE_T IS TABLE OF ORDERS.ITEM_CODE%TYPE INDEX BY BINARY_INTEGER;
L_ITEM_CODE ITEM_CODE_T;
BEFORE STATEMENT IS
CURSOR C IS
SELECT ORD_ID, ITEM_CODE
FROM ORDERS;
BEGIN
FOR I IN C
LOOP
L_ITEM_CODE(I.ORD_ID):=I.ITEM_CODE;
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Item code changed from ‘||L_ITEM_CODE(:OLD.ORD_ID)||’ to ‘||:NEW.ITEM_CODE);
END AFTER EACH ROW;
END;
/
FOR UPDATE ON ORDERS
COMPOUND TRIGGER
TYPE ITEM_CODE_T IS TABLE OF ORDERS.ITEM_CODE%TYPE INDEX BY BINARY_INTEGER;
L_ITEM_CODE ITEM_CODE_T;
BEFORE STATEMENT IS
CURSOR C IS
SELECT ORD_ID, ITEM_CODE
FROM ORDERS;
BEGIN
FOR I IN C
LOOP
L_ITEM_CODE(I.ORD_ID):=I.ITEM_CODE;
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Item code changed from ‘||L_ITEM_CODE(:OLD.ORD_ID)||’ to ‘||:NEW.ITEM_CODE);
END AFTER EACH ROW;
END;
/
UPDATE ORDERS
SET ITEM_CODE = ‘Item Number:’||ORD_ID
WHERE ORD_ID=100;
Item code changed from Prod 99 to Item Number:100
1 row updated.
SET ITEM_CODE = ‘Item Number:’||ORD_ID
WHERE ORD_ID=100;
Item code changed from Prod 99 to Item Number:100
1 row updated.
COMPOUND TRIGGERS OTHER EXAMPLES:
CREATE
OR REPLACE TRIGGER <trigger-name>
FOR <trigger-action> ON
<table-name>
COMPOUND TRIGGER
-- Global declaration.
g_global_variableVARCHAR2(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>;
/
The
following code creates a test table and a compound trigger that fires for each
timing point associated with insert, update and delete statements. The
triggering actions are logged in a PL/SQL table defined in the global
declaration section. The final timing point for each statement prints out the
content of the PL/SQL table to show that the variable state has been maintained
throughout the lifetime of the statement.
CREATE
TABLE compound_trigger_test (
id NUMBER,
description VARCHAR2(50)
);
CREATE
OR REPLACE TRIGGER compound_trigger_test_trg
FOR INSERT OR UPDATE OR DELETE ON
compound_trigger_test
COMPOUND TRIGGER
-- Global declaration.
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tabt_tab
:= t_tab();
BEFORE STATEMENT IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last)
:= 'BEFORE STATEMENT - INSERT';
WHEN UPDATING THEN
l_tab(l_tab.last)
:= 'BEFORE STATEMENT - UPDATE';
WHEN DELETING THEN
l_tab(l_tab.last)
:= 'BEFORE STATEMENT - DELETE';
END CASE;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last)
:= 'BEFORE EACH ROW - INSERT (new.id=' || :new.id || ')';
WHEN UPDATING THEN
l_tab(l_tab.last)
:= 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id ||
')';
WHEN DELETING THEN
l_tab(l_tab.last)
:= 'BEFORE EACH ROW - DELETE (old.id=' || :old.id || ')';
END CASE;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last)
:= 'AFTER EACH ROW - INSERT (new.id=' || :new.id || ')';
WHEN UPDATING THEN
l_tab(l_tab.last)
:= 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id ||
')';
WHEN DELETING THEN
l_tab(l_tab.last)
:= 'AFTER EACH ROW - DELETE (old.id=' || :old.id || ')';
END CASE;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
l_tab.extend;
CASE
WHEN INSERTING THEN
l_tab(l_tab.last)
:= 'AFTER STATEMENT - INSERT';
WHEN UPDATING THEN
l_tab(l_tab.last)
:= 'AFTER STATEMENT - UPDATE';
WHEN DELETING THEN
l_tab(l_tab.last)
:= 'AFTER STATEMENT - DELETE';
END CASE;
FOR i IN l_tab.first ..l_tab.last LOOP
DBMS_OUTPUT.put_line(l_tab(i));
END LOOP;
l_tab.delete;
END AFTER STATEMENT;
END
compound_trigger_test_trg;
/
By
issuing several insert, update and delete statements against the test table we
can see that the compound trigger is working as expected.
SQL>
SET SERVEROUTPUT ON
SQL>
INSERT INTO compound_trigger_test VALUES (1, 'ONE');
BEFORE
STATEMENT - INSERT
BEFORE
EACH ROW - INSERT (new.id=1)
AFTER
EACH ROW - INSERT (new.id=1)
AFTER
STATEMENT - INSERT
1
row created.
SQL>
INSERT INTO compound_trigger_test VALUES (2, 'TWO');
BEFORE
STATEMENT - INSERT
BEFORE
EACH ROW - INSERT (new.id=2)
AFTER
EACH ROW - INSERT (new.id=2)
AFTER
STATEMENT - INSERT
1
row created.
SQL>
UPDATE compound_trigger_test SET id = id;
BEFORE
STATEMENT - UPDATE
BEFORE
EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER
EACH ROW - UPDATE (new.id=2 old.id=2)
BEFORE
EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER
EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER
STATEMENT - UPDATE
2
rows updated.
SQL>
DELETE FROM compound_trigger_test;
BEFORE
STATEMENT - DELETE
BEFORE
EACH ROW - DELETE (old.id=2)
AFTER
EACH ROW - DELETE (old.id=2)
BEFORE
EACH ROW - DELETE (old.id=1)
AFTER
EACH ROW - DELETE (old.id=1)
AFTER
STATEMENT - DELETE
2
rows deleted.
SQL>
Don't
forget to clean up the test table.
DROP
TABLE compound_trigger_test;
For
a more practical use of compound triggers, we can take the example quoted in
the Mutating Table Exceptions article and replace the two triggers and the package with a
single compound trigger, as shown below.
CREATE
OR REPLACE TRIGGER tab1_compound_trigger
FOR INSERT OR UPDATE ON tab1
COMPOUND TRIGGER
TYPE t_change_tab IS TABLE OF
tab1_audit%ROWTYPE;
g_change_tabt_change_tab
:= t_change_tab();
AFTER EACH ROW IS
BEGIN
g_change_tab.extend;
g_change_tab(g_change_tab.last).id := tab1_audit_seq.NEXTVAL;
IF INSERTING THEN
g_change_tab(g_change_tab.last).action := 'INSERT';
ELSE
g_change_tab(g_change_tab.last).action := 'UPDATE';
END IF;
g_change_tab(g_change_tab.last).tab1_id := :new.id;
g_change_tab(g_change_tab.last).created_time
:= SYSTIMESTAMP;
END AFTER EACH ROW;
AFTER STATEMENT IS
l_count NUMBER(10);
BEGIN
FOR i IN g_change_tab.first
..g_change_tab.last LOOP
SELECT COUNT(*)
INTO
g_change_tab(i).record_count
FROM
tab1;
END LOOP;
FORALL i IN g_change_tab.first
..g_change_tab.last
INSERT INTO tab1_audit VALUES g_change_tab(i);
g_change_tab.delete;
END AFTER STATEMENT;
END
tab1_compound_trigger;
/
From
a timing point perspective, the Compound Trigger Restrictions follow very closely with those of individual
statement and row level triggers. The main point of interest here is the
control of execution order. If multiple compound triggers are defined for the
same object, their order of execution can be controlled using the FOLLOWS clause, but this cannot
be used to control execution order when both compound and regular DML triggers
are defined against a single object. In such situations it is better to stick
with all DML triggers, or all compound triggers.
Enable and Disable Triggers
It
has been possible to enable and disable triggers for some time using the ALTER TRIGGER
and ALTER TABLE commands.
- ALTER TRIGGER
<trigger-name> DISABLE;
- ALTER TRIGGER
<trigger-name> ENABLE;
- ALTER TABLE <table-name>
DISABLE ALL TRIGGERS;
- ALTER TABLE <table-name>
ENABLE ALL TRIGGERS;
Prior
to 11g, it was only possible to create triggers in the enabled state, then
subsequently disable them. Now they can be explicitly enabled or disabled at
creation time, with the enabled state as the default.
CREATE
TABLE trigger_control_test (
id NUMBER,
description
VARCHAR2(50)
);
CREATE
OR REPLACE TRIGGER trigger_control_test_trg
BEFORE
INSERT ON trigger_control_test
FOR
EACH ROW
ENABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG
- Executed');
END;
/
SQL>
SET SERVEROUTPUT ON
SQL>
INSERT INTO trigger_control_test VALUES (1, 'ONE');
TRIGGER_CONTROL_TEST_TRG
- Executed
1
row created.
SQL>
CREATE
OR REPLACE TRIGGER trigger_control_test_trg
BEFORE
INSERT ON trigger_control_test
FOR
EACH ROW
DISABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG
- Executed');
END;
/
SQL>
INSERT INTO trigger_control_test VALUES (2, 'TWO');
1
row created.
SQL>
Don't
forget to clean up the test table.
DROP
TABLE trigger_control_test;
For
more information see:
- What's New in PL/SQL? - Oracle Database PL/SQL Language
Reference 11g Release 1 (11.1)
- CREATE TRIGGER
- Compound Triggers
Hope
this helps. Regards Tim...
No comments:
Post a Comment