Compound Triggers

In the earlier versions of Oracle, a user had to create separate triggers for each of the following timing points:

1. Before the trigger statement
2. After the trigger statement
3. Before each row changed by the trigger statement
4. After each row changed by the trigger statement

However in Oracle 11g, you can create a single trigger body for all timing points to share the common data. The compound triggers can act as statement level triggers as well as row level triggers. To use the compound triggers, you need to use package variables so that the data can be shared. In the compound triggers, there is a separate local declaration area for each timing point section. A compound trigger does not support the Exception section, but you can implement the Exception section for each timing point section.

The syntax for using the compound triggers is as follows:

CREATE OR REPLACE TRIGGER
FOR ON
COMPOUND TRIGGER
g_global_variable VARCHAR2(10); --Global declaration
BEFORE STATEMENT IS
BEGIN
statements;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
statements;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
statements;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
statements;
END AFTER STATEMENT;
END ;
/

The following example will illustrate how to create a compound trigger.

1. Enter the following statement in SQL *Plus to create the table EmpDemo:

CREATE TABLE EmpDemo as SELECT Empno, Ename, Sal, Hiredate
FROM Emp;

The above statement will create the table EmpDemo from the existing table Emp with the columns Empno, Ename, Sal, and Hiredate.

2. Next, enter the following statement in SQL *Plus to create another table with the name EmpAudit.

CREATE TABLE EmpAudit
(
Empno NUMBER(4),
ModOnDate DATE,
ModbyUser VARCHAR2(30),
old_Hiredate DATE,
new_Hiredate DATE
);

4. Next, enter the following PL/SQL code into the Notepad editor and then save the file at C:\oracle_11g\c08_oracle_11g with the file name tr_Emp_track.txt. Alternatively, enter the following code directly in SQL * Plus:

CREATE OR REPLACE trigger tr_Emp_track
FOR UPDATE OF Hiredate
ON EmpDemo
COMPOUND TRIGGER
TYPE ty_EMP_DETAILS IS TABLE OF EmpAudit%ROWTYPE
INDEX BY PLS_INTEGER;
Tab_Emp_Details ty_EMP_DETAILS;
ctr pls_integer := 0;
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before statement');
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before each row');
END BEFORE EACH ROW;
AFTER EACH ROW IS
8-66 Learning Oracle 11g: A PL/SQL Approach
BEGIN
ctr := ctr + 1;
DBMS_OUTPUT.PUT_LINE('After each row. Empno='||:new.Empno);
Tab_Emp_Details(ctr).Empno := :new.Empno;
Tab_Emp_Details(ctr).ModOnDate := sysdate;
Tab_Emp_Details(ctr).ModbyUser := user;
Tab_Emp_Details(ctr).old_Hiredate := :old.Hiredate;
Tab_Emp_Details(ctr).new_Hiredate := :new.Hiredate;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('After statement');
FORALL counter IN 1..Tab_Emp_Details.count()
INSERT INTO EmpAudit
VALUES Tab_Emp_Details(counter);
END AFTER STATEMENT;
END tr_Emp_track;

3. Now, execute the contents of the file tr_Emp_track.txt to create the trigger tr_Emp_track. To do so, enter the following command in SQL *Plus:

SQL>@ ‘C:\oracle_11g\c08_oracle_11g\tr_Emp_track.txt’

4. After creating the trigger tr_Emp_track, enter the following statement to update the record of the EmpDemo table:

UPDATE EmpDemo SET Hiredate = SYSDATE WHERE Empno = 7499;

After executing the above UPDATE statement, the trigger tr_Emp_track will be fired and it will insert a row in the EmpAudit table. The output of the above statement is as follows:

Before statement
Before each row
After each row. Empno = 7499
After statement
1 row updated.

5. Now, enter the following statement to check whether or not the record has been inserted

into the table EmpAudit.
SELECT * FROM EmpAudit;

The output of the above statement is as follows:

EMPNO MODONDATE MODBYUSER OLD_HIREDATE NEW_HIREDATE
----------- --------------------- -------------------- ------------------------ --------------------------
7499 13-OCT-09 SCOTT 20-FEB-81 13-OCT-09