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.