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:

1. BEFORE STATEMENT
2. BEFORE EACH ROW
3. AFTER EACH ROW
4. 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;
/

Important: All four timing sections are optional. You can use only the sections required for your business logic.

2. Four Timing Sections in a Compound Trigger

Let's understand each timing section.

Compound Trigger Execution Flow

1. BEFORE STATEMENT: Executes once before the DML statement starts processing rows.
2. BEFORE EACH ROW: Executes before each affected row is modified.
3. DML Operation on Row: Oracle performs INSERT, UPDATE or DELETE.
4. AFTER EACH ROW: Executes after each affected row is modified.
5. 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 STATEMENT

Important Observations

For three updated rows:

Timing Section	Number of Executions
BEFORE STATEMENT	1
BEFORE EACH ROW	3
AFTER EACH ROW	3
AFTER STATEMENT	1

Interview Point: Statement-level sections execute once per DML statement, whereas row-level sections execute once per affected row.

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 it

Practical 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 mutating

Why Does This Happen?

1. UPDATE is modifying the EMPLOYEES table.
2. The AFTER EACH ROW trigger executes.
3. The trigger tries to SELECT from EMPLOYEES.
4. 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.

Important Interview Concept: The main solution is to collect information in the row-level section and query the mutating table in the AFTER STATEMENT section.

Note: The example above is for understanding. In production, you should also eliminate duplicate department IDs to avoid calculating the same department total multiple times.

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: 5

How Does It Work?

1. v_count is initialized once for the triggering statement.
2. AFTER EACH ROW increments the variable.
3. After all rows are processed, AFTER STATEMENT reads the same variable.
4. 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.

Important: The audit records are part of the same transaction as the employee update. If the transaction is rolled back, the audit inserts are also rolled back.

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:

• INSERTING identifies INSERT operations.
• UPDATING identifies UPDATE operations.
• DELETING identifies 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

Important: Compound triggers do not automatically make every operation faster. Their performance benefits depend on how you use collections, bulk processing and SQL operations.

9. Important Rules and Restrictions

Remember these Oracle compound trigger rules:

1. A compound trigger must be created on a table or editioning view.
2. It supports INSERT, UPDATE and DELETE events.
3. It can contain up to four timing sections.
4. All timing sections share the common declaration section.
5. Variables declared in the common declaration section exist for the duration of the triggering statement.
6. A compound trigger cannot be autonomous.
7. You cannot use :OLD and :NEW in statement-level sections.
8. You can use :OLD and :NEW in row-level sections.
9. A compound trigger does not have a separate DECLARE keyword for its common declaration section.
10. 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

1. If an UPDATE statement modifies 10 rows, how many times does AFTER STATEMENT execute?

A. 10 times
B. Once
C. 11 times
D. Zero

2. Where should you query the same mutating table after collecting row information?

A. BEFORE EACH ROW
B. AFTER EACH ROW
C. AFTER STATEMENT
D. BEFORE STATEMENT

3. Which section is suitable for collecting :OLD and :NEW values?

A. BEFORE STATEMENT
B. AFTER EACH ROW
C. AFTER STATEMENT
D. Declaration section

4. Can variables be shared between sections of a compound trigger?

A. Yes
B. No
C. Only global variables
D. Only package variables

5. Can we execute COMMIT directly inside a compound trigger?

A. Yes
B. Only in AFTER STATEMENT
C. No
D. Only with FORALL

Quiz Answers

Click here to view the correct answers

1. B. Once
2. C. AFTER STATEMENT
3. B. AFTER EACH ROW
4. A. Yes
5. C. No

Final Interview Tip

If an interviewer asks, "How do you solve ORA-04091 using a compound trigger?", explain this exact approach:

1. Collect required row values in AFTER EACH ROW.
2. Store them in a collection declared in the common declaration section.
3. Access the mutating table in AFTER STATEMENT.
4. 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.