Compound Trigger in Oracle SQL

What is a Compound Trigger in Oracle SQL?

A compound trigger in Oracle SQL is a single trigger that combines multiple timing points (e.g., BEFORE STATEMENT, AFTER EACH ROW, AFTER STATEMENT) within a single structure. It was introduced in Oracle 11g to simplify managing complex trigger logic and improve performance.

---

Key Features of a Compound Trigger

1. Multiple Timing Points:

• Allows you to define logic for BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, and AFTER EACH ROW within one trigger.

2. Shared State:

• Data declared in the trigger’s declarative section persists across timing points, enabling shared variables and collections.

3. Performance Optimization:

• Useful for bulk processing with BULK COLLECT and FORALL, reducing context switches between PL/SQL and SQL layers.

4. Simplified Logic:

• Consolidates multiple triggers into one, improving readability and maintainability.

---

Why Use a Compound Trigger?

In traditional triggers, row-by-row operations (like FOR EACH ROW) can cause significant performance issues for large datasets due to context switches. A compound trigger allows:

• Row-wise processing during AFTER EACH ROW to capture data.
• Bulk operations in the AFTER STATEMENT phase, optimizing the interaction with the database.

---

Example: Updating 1000 Records with a Compound Trigger

Scenario

We want to update 1000 rows in an employees table and log changes to an employee_audit table. Using a compound trigger, we collect the audit data row by row and insert it in bulk.

---

Step 1: Create Tables

1. employees Table:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    department_id NUMBER,
    salary NUMBER
);

---

2. employee_audit Table:

CREATE TABLE employee_audit (
    audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    employee_id NUMBER,
    action VARCHAR2(50),
    change_date DATE
);

---

Step 2: Insert Sample Data

BEGIN
    FOR i IN 1..1000 LOOP
        INSERT INTO employees (employee_id, employee_name, department_id, salary)
        VALUES (i, 'Employee ' || i, MOD(i, 5) + 1, 45000 + (i * 10));
    END LOOP;
END;

---

Step 3: Create a Compound Trigger

CREATE OR REPLACE TRIGGER update_compound_trigger
FOR UPDATE ON employees
COMPOUND TRIGGER
  -- Declare a collection for bulk processing
  TYPE audit_data_type IS TABLE OF employee_audit%ROWTYPE;
  audit_data audit_data_type := audit_data_type();

  BEFORE STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Trigger execution started.');
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    -- Add row data to the collection
    audit_data.EXTEND;
    audit_data(audit_data.LAST).employee_id := :OLD.employee_id;
    audit_data(audit_data.LAST).action := 'UPDATE';
    audit_data(audit_data.LAST).change_date := SYSDATE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Perform bulk insert
    IF audit_data.COUNT > 0 THEN
      FORALL i IN 1..audit_data.COUNT
        INSERT INTO employee_audit VALUES audit_data(i);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Trigger execution completed.');
  END AFTER STATEMENT;
END update_compound_trigger;

---

Step 4: Test the Trigger

1. Update Records:

UPDATE employees SET salary = salary + 5000;

1. Verify the employee_audit Table:

SELECT COUNT(*) FROM employee_audit; -- Should return 1000 rows

1. View Sample Audit Data:

SELECT * FROM employee_audit WHERE ROWNUM <= 10;

---

How It Works

1. BEFORE STATEMENT: Executes once before any rows are processed. Here, it initializes or logs the start of the operation.

2. AFTER EACH ROW: Executes for each row affected by the UPDATE. Each row's data is added to the audit_data collection.

3. AFTER STATEMENT: Executes once after all rows are processed. This is where the bulk INSERT operation (FORALL) takes place.

---

Advantages of the Compound Trigger

1. Improved Performance

2. Reduces 1000 context switches (one per row) to a single bulk operation.

3. Cleaner Logic:

4. Combines logic for different timing points in one place.

5. Scalable:

6. Ideal for handling large datasets.

---

Performance Comparison

• Without Compound Trigger: 1000 INSERT operations occur individually during row processing, leading to significant overhead.
• With Compound Trigger: Row data is collected in memory and processed in bulk, resulting in faster execution.

---