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.

1. Shared State:

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

1. Performance Optimization:

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

1. 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
);

1. 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

Timing Points

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:

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

1. Cleaner Logic:

Combines logic for different timing points in one place.

1. Scalable:

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.

---

This example demonstrates how a compound trigger is an effective tool for bulk processing in Oracle SQL. Let me know if you’d like further clarification!