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
- Multiple Timing Points:
Allows you to define logic for BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, and AFTER EACH ROW within one trigger.
- Shared State:
Data declared in the trigger’s declarative section persists across timing points, enabling shared variables and collections.
- Performance Optimization:
Useful for bulk processing with BULK COLLECT and FORALL, reducing context switches between PL/SQL and SQL layers.
- 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
- employees Table:
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(100),
department_id NUMBER,
salary NUMBER
);
- 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
- Update Records:
UPDATE employees SET salary = salary + 5000;
- Verify the employee_audit Table:
SELECT COUNT(*) FROM employee_audit; -- Should return 1000 rows
- View Sample Audit Data:
SELECT * FROM employee_audit WHERE ROWNUM <= 10;
How It Works
Timing Points
BEFORE STATEMENT: Executes once before any rows are processed. Here, it initializes or logs the start of the operation.
AFTER EACH ROW: Executes for each row affected by the UPDATE. Each row's data is added to the audit_data collection.
AFTER STATEMENT: Executes once after all rows are processed. This is where the bulk INSERT operation (FORALL) takes place.
Advantages of the Compound Trigger
- Improved Performance:
Reduces 1000 context switches (one per row) to a single bulk operation.
- Cleaner Logic:
Combines logic for different timing points in one place.
- 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!
SOCIAL SHARE CARD GENERATOR