Oracle Compound Trigger – Complete Notes with Examples
A Compound Trigger in Oracle is a special type of PL/SQL trigger that allows you to combine multiple trigger timing points into a single trigger.
It can execute code at different stages of a DML operation (INSERT, UPDATE, DELETE) on a table.
The biggest advantage of a compound trigger is that it allows you to share common variables and data between different timing sections of the same trigger.
It is especially useful for:
- Avoiding mutating table errors.
- Collecting multiple row changes and processing them together.
- Reducing repeated SQL statements.
- Improving performance during bulk DML operations.
- Maintaining audit information.
Let's understand it step by step with practical examples.
1. What is a Compound Trigger?
Normally, Oracle supports different types of triggers:
- BEFORE STATEMENT
- BEFORE EACH ROW
- AFTER EACH ROW
- AFTER STATEMENT
Traditionally, we create separate triggers for these timing points.
But a compound trigger allows us to write all these sections inside a single trigger.
Syntax:
CREATE OR REPLACE TRIGGER trigger_name
FOR INSERT OR UPDATE OR DELETE
ON table_name
COMPOUND TRIGGER
-- Declaration section
-- Common variables
BEFORE STATEMENT IS
BEGIN
-- Executes before DML statement
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
-- Executes before each affected row
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
-- Executes after each affected row
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
-- Executes after all affected rows
END AFTER STATEMENT;
END trigger_name;
/2. Four Timing Sections in a Compound Trigger
Let's understand each timing section.
Compound Trigger Execution Flow
- BEFORE STATEMENT: Executes once before the DML statement starts processing rows.
- BEFORE EACH ROW: Executes before each affected row is modified.
- DML Operation on Row: Oracle performs INSERT, UPDATE or DELETE.
- AFTER EACH ROW: Executes after each affected row is modified.
- AFTER STATEMENT: Executes once after all affected rows are processed.
A. BEFORE STATEMENT
- Executes only once for the entire DML statement.
- Executes before Oracle starts processing individual rows.
- Useful for initializing variables and collections.
Example:
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before statement');
END BEFORE STATEMENT;B. BEFORE EACH ROW
- Executes once for every affected row.
- Executes before the actual row modification.
- Useful for validating data and assigning values to :NEW.
Example:
BEFORE EACH ROW IS
BEGIN
:NEW.salary := NVL(:NEW.salary, 0);
END BEFORE EACH ROW;C. AFTER EACH ROW
- Executes once for every affected row.
- Executes after the row modification.
- Useful for collecting old and new values.
Example:
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Employee salary updated'
);
END AFTER EACH ROW;D. AFTER STATEMENT
- Executes only once after all affected rows have been processed.
- Useful for performing summary operations and bulk processing.
Example:
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('After statement');
END AFTER STATEMENT;3. First Practical Example – Display Trigger Execution Order
Let's create an employee table.
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER
);Insert sample records:
INSERT INTO employees VALUES (1, 'John', 50000);
INSERT INTO employees VALUES (2, 'David', 40000);
INSERT INTO employees VALUES (3, 'Smith', 60000);
COMMIT;Now create a compound trigger.
CREATE OR REPLACE TRIGGER emp_comp_trg
FOR UPDATE ON employees
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'1. BEFORE STATEMENT'
);
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'2. BEFORE EACH ROW - ' || :OLD.emp_name
);
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'3. AFTER EACH ROW - ' || :OLD.emp_name
);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'4. AFTER STATEMENT'
);
END AFTER STATEMENT;
END emp_comp_trg;
/Execute:
SET SERVEROUTPUT ON;
UPDATE employees
SET salary = salary + 1000;Output:
1. BEFORE STATEMENT
2. BEFORE EACH ROW - John
3. AFTER EACH ROW - John
2. BEFORE EACH ROW - David
3. AFTER EACH ROW - David
2. BEFORE EACH ROW - Smith
3. AFTER EACH ROW - Smith
4. AFTER STATEMENTImportant Observations
For three updated rows:
| Timing Section | Number of Executions |
|---|---|
| BEFORE STATEMENT | 1 |
| BEFORE EACH ROW | 3 |
| AFTER EACH ROW | 3 |
| AFTER STATEMENT | 1 |
4. Most Important Example – Avoid Mutating Table Error
This is one of the most important interview topics related to compound triggers.
What is a Mutating Table Error?
A mutating table error occurs when a row-level trigger tries to query or modify the same table that is currently being modified by the triggering DML statement.
Oracle generally raises:
ORA-04091: table is mutating,
trigger/function may not see itPractical Scenario
Suppose we have an EMPLOYEES table.
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);Business Requirement:
Whenever an employee's salary is updated, we want to check the total salary of that employee's department.
- IT department has three employees.
- Their total salary is ₹150,000.
- When one employee's salary changes, we want to calculate the department's new total salary.
Step 1: Create a Normal Trigger
CREATE OR REPLACE TRIGGER emp_salary_trg
AFTER UPDATE OF salary
ON employees
FOR EACH ROW
DECLARE
v_total_salary NUMBER;
BEGIN
SELECT SUM(salary)
INTO v_total_salary
FROM employees
WHERE department_id = :NEW.department_id;
DBMS_OUTPUT.PUT_LINE(
'Total salary: ' || v_total_salary
);
END;
/Step 2: Execute UPDATE
UPDATE employees
SET salary = salary + 5000
WHERE emp_id = 101;Oracle may return:
ORA-04091: table EMPLOYEES is mutatingWhy Does This Happen?
- UPDATE is modifying the EMPLOYEES table.
- The AFTER EACH ROW trigger executes.
- The trigger tries to SELECT from EMPLOYEES.
- Oracle prevents this row-level access to the mutating table.
Step 3: Solve Using Compound Trigger
We collect the department IDs during row-level processing.
Then we query the EMPLOYEES table in the AFTER STATEMENT section.
At that time, the row-level processing is complete, so the table is no longer mutating for this statement.
CREATE OR REPLACE TRIGGER emp_salary_comp_trg
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
TYPE dept_type IS TABLE OF
employees.department_id%TYPE
INDEX BY PLS_INTEGER;
v_dept_ids dept_type;
v_count NUMBER := 0;
AFTER EACH ROW IS
BEGIN
v_count := v_count + 1;
v_dept_ids(v_count) :=
:NEW.department_id;
END AFTER EACH ROW;
AFTER STATEMENT IS
v_total_salary NUMBER;
BEGIN
FOR i IN 1 .. v_count LOOP
SELECT SUM(salary)
INTO v_total_salary
FROM employees
WHERE department_id = v_dept_ids(i);
DBMS_OUTPUT.PUT_LINE(
'Department: ' ||
v_dept_ids(i) ||
' Total Salary: ' ||
v_total_salary
);
END LOOP;
END AFTER STATEMENT;
END emp_salary_comp_trg;
/Now execute:
UPDATE employees
SET salary = salary + 5000
WHERE department_id = 10;The trigger collects department IDs while processing each row.
After all rows are updated, it calculates the department salary.
5. Compound Trigger with Shared Variables
One of the biggest advantages of a compound trigger is sharing variables between timing sections.
In a normal trigger, separate triggers have separate declaration sections.
In a compound trigger, common variables are declared once and shared across all timing sections.
Example
Requirement: Count how many employee records are updated by a single UPDATE statement.
CREATE OR REPLACE TRIGGER emp_count_trg
FOR UPDATE ON employees
COMPOUND TRIGGER
v_count NUMBER := 0;
AFTER EACH ROW IS
BEGIN
v_count := v_count + 1;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Total updated employees: ' || v_count
);
END AFTER STATEMENT;
END emp_count_trg;
/Execute:
UPDATE employees
SET salary = salary + 1000;Suppose there are 5 employee records.
Output:
Total updated employees: 5How Does It Work?
v_countis initialized once for the triggering statement.- AFTER EACH ROW increments the variable.
- After all rows are processed, AFTER STATEMENT reads the same variable.
- It displays the total number of updated records.
This is called sharing common state between timing sections.
6. Compound Trigger for Audit Table
Another real-time use case is maintaining an audit table.
Requirement: Whenever an employee's salary changes, store:
- Employee ID
- Old salary
- New salary
- Change date
Step 1: Create Employee Table
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER
);Step 2: Create Audit Table
CREATE TABLE emp_salary_audit (
emp_id NUMBER,
old_salary NUMBER,
new_salary NUMBER,
changed_date DATE
);Step 3: Create Compound Trigger
CREATE OR REPLACE TRIGGER emp_audit_comp_trg
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
TYPE audit_record IS RECORD (
emp_id NUMBER,
old_salary NUMBER,
new_salary NUMBER
);
TYPE audit_table IS TABLE OF audit_record
INDEX BY PLS_INTEGER;
v_audit audit_table;
v_count NUMBER := 0;
AFTER EACH ROW IS
BEGIN
v_count := v_count + 1;
v_audit(v_count).emp_id := :NEW.emp_id;
v_audit(v_count).old_salary := :OLD.salary;
v_audit(v_count).new_salary := :NEW.salary;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FORALL i IN 1 .. v_count
INSERT INTO emp_salary_audit
VALUES (
v_audit(i).emp_id,
v_audit(i).old_salary,
v_audit(i).new_salary,
SYSDATE
);
END AFTER STATEMENT;
END emp_audit_comp_trg;
/Step 4: Execute UPDATE
UPDATE employees
SET salary = salary + 5000;Step 5: Check Audit Table
SELECT *
FROM emp_salary_audit;Sample Output:
| EMP_ID | OLD_SALARY | NEW_SALARY |
|---|---|---|
| 101 | 50000 | 55000 |
| 102 | 40000 | 45000 |
| 103 | 60000 | 65000 |
Why Use FORALL?
FORALL performs bulk DML operations using collection elements.
Instead of executing an individual INSERT for every employee in a PL/SQL loop, it sends the inserts in bulk.
This can reduce PL/SQL-to-SQL context switching and improve performance.
7. Compound Trigger with INSERT, UPDATE and DELETE
A compound trigger can support multiple DML operations.
Example:
CREATE OR REPLACE TRIGGER emp_all_dml_trg
FOR INSERT OR UPDATE OR DELETE
ON employees
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(
'Employee inserted'
);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(
'Employee updated'
);
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE(
'Employee deleted'
);
END IF;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'DML statement completed'
);
END AFTER STATEMENT;
END emp_all_dml_trg;
/Here:
INSERTINGidentifies INSERT operations.UPDATINGidentifies UPDATE operations.DELETINGidentifies DELETE operations.
These are conditional predicates available in triggers.
8. Compound Trigger vs Normal Trigger
This is a frequently asked interview question.
| Feature | Normal Trigger | Compound Trigger |
|---|---|---|
| Timing sections | One timing point per trigger | Multiple timing points |
| Shared variables | Not shared across separate triggers | Shared across timing sections |
| Mutating table solution | More difficult | Easier to implement |
| Bulk processing | Possible with additional design | Convenient with collections and FORALL |
| Code organization | Multiple triggers may be required | Related logic in one trigger |
| Performance | Depends on implementation | Can reduce repeated operations |
9. Important Rules and Restrictions
Remember these Oracle compound trigger rules:
- A compound trigger must be created on a table or editioning view.
- It supports INSERT, UPDATE and DELETE events.
- It can contain up to four timing sections.
- All timing sections share the common declaration section.
- Variables declared in the common declaration section exist for the duration of the triggering statement.
- A compound trigger cannot be autonomous.
- You cannot use
:OLDand:NEWin statement-level sections. - You can use
:OLDand:NEWin row-level sections. - A compound trigger does not have a separate
DECLAREkeyword for its common declaration section. - If the triggering statement fails and rolls back, the compound trigger's database changes are also rolled back.
10. Important Difference: Compound Trigger vs Statement-Level Trigger
| Feature | Statement-Level Trigger | Compound Trigger |
|---|---|---|
| Execution | Once per statement | Multiple timing points |
| Row processing | No row-level access | Can process individual rows |
| Shared collection | Not applicable across separate triggers | Yes |
| AFTER STATEMENT | Yes | Yes |
| BEFORE EACH ROW | No | Yes |
A compound trigger is not a replacement for every normal trigger. Use it when multiple timing points or shared state are useful.
11. Frequently Asked Oracle Compound Trigger Interview Questions
Q1. What is a compound trigger?
A compound trigger is a PL/SQL trigger that combines multiple timing sections into one trigger and allows common variables to be shared across them.
Q2. What are the four timing sections?
- BEFORE STATEMENT
- BEFORE EACH ROW
- AFTER EACH ROW
- AFTER STATEMENT
Q3. Can we use all four sections in one compound trigger?
Yes. All four sections are optional, so you can use any combination.
Q4. What is the biggest advantage of a compound trigger?
Sharing variables between timing sections and avoiding mutating table errors by moving table queries to the AFTER STATEMENT section.
Q5. Can we use COMMIT inside a compound trigger?
No. A normal trigger cannot execute COMMIT or ROLLBACK directly.
Q6. Can we use autonomous transactions?
A compound trigger itself cannot be autonomous. Its individual timing sections cannot be declared autonomous either.
Q7. Can we use :OLD and :NEW in BEFORE STATEMENT?
No. They are available in row-level timing sections.
Q8. Can we use collections in compound triggers?
Yes. Collections are commonly used to collect row information and process it in AFTER STATEMENT.
Q9. Can a compound trigger prevent mutating table errors?
It can help avoid them by collecting row information and accessing the affected table after row-level processing is complete. However, it does not automatically eliminate every possible mutating-table situation.
Q10. What happens if an UPDATE statement updates 100 rows?
- BEFORE STATEMENT executes once.
- BEFORE EACH ROW executes 100 times.
- AFTER EACH ROW executes 100 times.
- AFTER STATEMENT executes once.
12. Quick Revision Notes
| Topic | Remember |
|---|---|
| Compound trigger | Multiple timing sections in one trigger |
| Main advantage | Shared variables |
| Mutating table solution | Collect in row section, query in AFTER STATEMENT |
| BEFORE STATEMENT | Executes once |
| BEFORE EACH ROW | Executes for every affected row |
| AFTER EACH ROW | Executes for every affected row |
| AFTER STATEMENT | Executes once |
| Collections | Store row-level information |
| FORALL | Bulk DML processing |
| COMMIT | Not allowed directly |
| Autonomous transaction | Not allowed for compound trigger |
13. Practice Questions
Try these questions to check your understanding of Oracle Compound Triggers.
Oracle Compound Trigger Quiz
A. 10 times
B. Once
C. 11 times
D. Zero
A. BEFORE EACH ROW
B. AFTER EACH ROW
C. AFTER STATEMENT
D. BEFORE STATEMENT
A. BEFORE STATEMENT
B. AFTER EACH ROW
C. AFTER STATEMENT
D. Declaration section
A. Yes
B. No
C. Only global variables
D. Only package variables
A. Yes
B. Only in AFTER STATEMENT
C. No
D. Only with FORALL
Quiz Answers
Click here to view the correct answers
- B. Once
- C. AFTER STATEMENT
- B. AFTER EACH ROW
- A. Yes
- C. No
Final Interview Tip
If an interviewer asks, "How do you solve ORA-04091 using a compound trigger?", explain this exact approach:
- Collect required row values in
AFTER EACH ROW. - Store them in a collection declared in the common declaration section.
- Access the mutating table in
AFTER STATEMENT. - Perform the required calculations or DML operations after all rows have been processed.
This is one of the most important practical uses of Oracle compound triggers.