Absolutely. Here’s a practical Oracle Compound Trigger FAQ with examples, aimed at interviews and real-world PL/SQL.
A compound trigger is a special Oracle trigger that can contain multiple timing sections in a single trigger. It is especially useful for sharing state between row-level and statement-level processing and for solving many mutating-table scenarios.
Basic Structure
CREATE OR REPLACE TRIGGER trigger_name
FOR INSERT OR UPDATE OR DELETE ON table_name
COMPOUND TRIGGER
-- Common variables
-- Shared by all timing sections
BEFORE STATEMENT IS
BEGIN
...
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
...
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
...
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
...
END AFTER STATEMENT;
END trigger_name;
/Important: Not every timing section is mandatory.
1. What is a compound trigger?
A compound trigger combines multiple trigger timing points into one trigger.
For example:
BEFORE STATEMENT
↓
BEFORE EACH ROW
↓
AFTER EACH ROW
↓
AFTER STATEMENTInstead of creating four separate triggers, related logic can be placed into one compound trigger.
Example:
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before statement');
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE('After row');
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('After statement');
END AFTER STATEMENT;
END trg_emp;
/2. Why do we need compound triggers?
One of the most important uses of compound triggers is handling mutating-table scenarios.
Consider this row-level trigger:
CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees;
END;
/This can result in:
ORA-04091: table EMPLOYEES is mutatingA compound trigger allows you to collect information during row processing and perform the table-level processing after the statement.
AFTER EACH ROW
↓
Collect information
↓
AFTER STATEMENT
↓
Query/process EMPLOYEES3. What are the timing sections of a compound trigger?
For a DML compound trigger, the common timing sections are:
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
AFTER STATEMENTExample:
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
NULL;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
NULL;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
NULL;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
NULL;
END AFTER STATEMENT;
END trg_emp;
/You don't have to use all four sections.
4. Is a compound trigger row-level or statement-level?
This is a common interview trap.
A compound trigger can contain both statement-level and row-level timing sections.
| Timing Section | Type |
|---|---|
BEFORE STATEMENT |
Statement-level |
BEFORE EACH ROW |
Row-level |
AFTER EACH ROW |
Row-level |
AFTER STATEMENT |
Statement-level |
5. Can a compound trigger have only one timing section?
Yes.
For example:
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Employee updated'
);
END AFTER EACH ROW;
END trg_emp;
/This is a valid compound trigger.
6. What is the biggest advantage of a compound trigger?
One of the biggest advantages is the ability to share state between timing sections.
For example:
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
g_emp_ids t_emp_ids := t_emp_ids();
AFTER EACH ROW IS
BEGIN
g_emp_ids.EXTEND;
g_emp_ids(g_emp_ids.COUNT) := :NEW.employee_id;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN 1 .. g_emp_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
'Employee ID = ' || g_emp_ids(i)
);
END LOOP;
END AFTER STATEMENT;
END trg_emp;
/The collection g_emp_ids is available to both sections during the triggering statement.
7. How does state sharing work?
Suppose the following statement updates three employees:
UPDATE employees
SET salary = salary + 1000;The execution is conceptually:
BEFORE STATEMENT
↓
Initialize shared state
↓
AFTER EACH ROW
↓
Employee 1 added
↓
AFTER EACH ROW
↓
Employee 2 added
↓
AFTER EACH ROW
↓
Employee 3 added
↓
AFTER STATEMENT
↓
Process all 3 employeesThis shared-state capability is one of the most powerful features of compound triggers.
8. Does the shared state survive for the entire statement?
Yes.
Variables declared in the compound trigger's declarative section can be shared by the timing sections during that triggering statement.
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
g_count NUMBER := 0;
AFTER EACH ROW IS
BEGIN
g_count := g_count + 1;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Rows affected = ' || g_count
);
END AFTER STATEMENT;
END trg_emp;
/If 500 rows are updated:
Rows affected = 5009. What happens to compound-trigger variables after the statement?
They represent statement-duration state.
After the triggering statement completes, that state is not retained for a future statement.
UPDATE statement #1
↓
g_count = 100
↓
Statement finishes
↓
State is discarded
UPDATE statement #2
↓
New trigger execution
↓
New shared stateThis is an important difference when comparing compound-trigger state with package-level global variables.
10. How can a compound trigger solve a mutating-table problem?
Suppose we want to collect employee IDs during an update and then query employees after the update.
Compound trigger:
CREATE OR REPLACE TRIGGER trg_emp_check
FOR UPDATE ON employees
COMPOUND TRIGGER
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
g_emp_ids t_emp_ids := t_emp_ids();
AFTER EACH ROW IS
BEGIN
g_emp_ids.EXTEND;
g_emp_ids(g_emp_ids.COUNT) := :NEW.employee_id;
END AFTER EACH ROW;
AFTER STATEMENT IS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees;
DBMS_OUTPUT.PUT_LINE(
'Employee count = ' || v_count
);
END AFTER STATEMENT;
END trg_emp_check;
/The important pattern is:
ROW SECTION
↓
Collect data
STATEMENT SECTION
↓
Query/process table11. Can we use :OLD and :NEW in every section?
No.
:OLD and :NEW are available in the row-level sections.
For example:
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEW.salary);
END AFTER EACH ROW;But you cannot use :NEW in an AFTER STATEMENT section because that section is not associated with an individual row.
BEFORE EACH ROW → :OLD / :NEW
AFTER EACH ROW → :OLD / :NEW
BEFORE STATEMENT → No :OLD / :NEW
AFTER STATEMENT → No :OLD / :NEW12. Can we modify :NEW in a compound trigger?
Yes, in a BEFORE EACH ROW section.
CREATE OR REPLACE TRIGGER trg_emp_upper
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:NEW.first_name := UPPER(:NEW.first_name);
END BEFORE EACH ROW;
END trg_emp_upper;
/You cannot modify :NEW in:
AFTER EACH ROW
AFTER STATEMENT13. Can a compound trigger handle INSERT, UPDATE and DELETE?
Yes.
CREATE OR REPLACE TRIGGER trg_emp
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
AFTER EACH ROW IS
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserted');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Updated');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleted');
END IF;
END AFTER EACH ROW;
END trg_emp;
/14. Can we use INSERTING, UPDATING, and DELETING?
Yes.
AFTER EACH ROW IS
BEGIN
IF INSERTING THEN
...
ELSIF UPDATING THEN
...
ELSIF DELETING THEN
...
END IF;
END AFTER EACH ROW;These conditions identify which DML operation caused the trigger to fire.
15. Can a compound trigger contain both BEFORE and AFTER logic?
Yes.
CREATE OR REPLACE TRIGGER trg_emp
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Starting DML'
);
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(
-20001,
'Salary cannot be negative'
);
END IF;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Employee processed'
);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'DML completed'
);
END AFTER STATEMENT;
END trg_emp;
/16. What is a real-world auditing example?
Suppose you want to collect changed employee IDs and insert audit records after the statement finishes.
Audit table:
CREATE TABLE employee_audit (
employee_id NUMBER,
action_type VARCHAR2(20),
audit_date TIMESTAMP
);Compound trigger:
CREATE OR REPLACE TRIGGER trg_emp_audit
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
g_emp_ids t_emp_ids := t_emp_ids();
AFTER EACH ROW IS
BEGIN
g_emp_ids.EXTEND;
IF INSERTING OR UPDATING THEN
g_emp_ids(g_emp_ids.COUNT) := :NEW.employee_id;
ELSE
g_emp_ids(g_emp_ids.COUNT) := :OLD.employee_id;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN 1 .. g_emp_ids.COUNT LOOP
INSERT INTO employee_audit
(
employee_id,
action_type,
audit_date
)
VALUES
(
g_emp_ids(i),
CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END,
SYSTIMESTAMP
);
END LOOP;
END AFTER STATEMENT;
END trg_emp_audit;
/The basic design is:
DML statement
↓
AFTER EACH ROW
↓
Collect IDs
↓
AFTER STATEMENT
↓
Insert audit records17. Can we use a collection inside a compound trigger?
Yes. This is extremely common.
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
TYPE t_ids IS TABLE OF NUMBER;
g_ids t_ids := t_ids();
AFTER EACH ROW IS
BEGIN
g_ids.EXTEND;
g_ids(g_ids.COUNT) := :NEW.employee_id;
END AFTER EACH ROW;
END trg_emp;
/Collections can be used to collect information during row processing and then process it later.
18. Why not simply use a package variable?
Before compound triggers, developers sometimes used package variables to maintain state between row-level and statement-level processing.
That approach can be complicated because package variables can have a broader session lifetime and require additional management.
A compound trigger naturally provides statement-duration state:
DML starts
↓
Compound trigger state created
↓
Rows processed
↓
Shared state available
↓
Statement completes
↓
State discarded19. Can a compound trigger have a WHEN clause?
A compound trigger does not use the traditional WHEN clause in the same way as a simple row trigger.
Instead, place the condition inside the relevant row-level section.
AFTER EACH ROW IS
BEGIN
IF :NEW.salary > 100000 THEN
DBMS_OUTPUT.PUT_LINE(
'High salary employee'
);
END IF;
END AFTER EACH ROW;20. Can a compound trigger be used on a view?
There are important Oracle restrictions around compound triggers and views, particularly with INSTEAD OF triggers on noneditioning views.
For normal interview purposes, remember:
Compound trigger on table
↓
Can combine multiple timing sections
INSTEAD OF trigger on view
↓
Has different rules and restrictionsDo not confuse a normal DML compound trigger with an INSTEAD OF trigger.
21. Can a compound trigger perform COMMIT?
No.
For example:
AFTER STATEMENT IS
BEGIN
COMMIT; -- ❌ Not allowed
END AFTER STATEMENT;Triggers execute as part of the transaction that caused them, so normal trigger transaction-control restrictions apply.
22. Can a compound trigger call a procedure?
Yes.
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
AFTER STATEMENT IS
BEGIN
process_employee_changes;
END AFTER STATEMENT;
END trg_emp;
/This can be useful when the processing logic is substantial. Keeping the trigger focused and moving reusable business logic into procedures can improve maintainability.
23. What happens if the DML statement affects zero rows?
This is an important interview question.
Suppose:
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = -9999;No rows are affected.
The statement-level sections can still execute:
BEFORE STATEMENT → executes
BEFORE EACH ROW → 0 times
AFTER EACH ROW → 0 times
AFTER STATEMENT → executes24. What happens if a row-level section raises an exception?
Suppose:
BEFORE EACH ROW IS
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(
-20001,
'Invalid salary'
);
END IF;
END BEFORE EACH ROW;If the exception propagates, the DML statement fails. It does not simply continue processing the remaining rows as though the failed row had been skipped.
25. What is the execution order of a compound trigger?
For a statement affecting multiple rows, the timing is conceptually:
BEFORE STATEMENT
↓
BEFORE EACH ROW
↓
AFTER EACH ROW
↓
BEFORE EACH ROW
↓
AFTER EACH ROW
↓
...
↓
AFTER STATEMENTFor three rows:
BEFORE STATEMENT
BEFORE EACH ROW → Row 1
AFTER EACH ROW → Row 1
BEFORE EACH ROW → Row 2
AFTER EACH ROW → Row 2
BEFORE EACH ROW → Row 3
AFTER EACH ROW → Row 3
AFTER STATEMENT26. What is a very common mutating-table interview question?
When an employee's salary changes, check the total salary of the department after the update.
A simple row-level trigger might try to execute:
SELECT SUM(salary)
FROM employees
WHERE department_id = :NEW.department_id;inside:
AFTER EACH ROWThis can cause a mutating-table problem.
Compound-trigger solution:
CREATE OR REPLACE TRIGGER trg_salary_check
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
TYPE t_depts IS TABLE OF employees.department_id%TYPE;
g_depts t_depts := t_depts();
AFTER EACH ROW IS
BEGIN
g_depts.EXTEND;
g_depts(g_depts.COUNT) :=
:NEW.department_id;
END AFTER EACH ROW;
AFTER STATEMENT IS
v_total_salary NUMBER;
BEGIN
FOR i IN 1 .. g_depts.COUNT LOOP
SELECT SUM(salary)
INTO v_total_salary
FROM employees
WHERE department_id = g_depts(i);
DBMS_OUTPUT.PUT_LINE(
'Department ' || g_depts(i) ||
' Total Salary = ' || v_total_salary
);
END LOOP;
END AFTER STATEMENT;
END trg_salary_check;
/The important design pattern is:
Don't query EMPLOYEES
during EACH ROW.
Collect what you need.
Then query EMPLOYEES
AFTER STATEMENT.27. Can we improve the previous example?
Yes. The previous example may process the same department multiple times.
For example:
Employee 1 → Department 10
Employee 2 → Department 10
Employee 3 → Department 20The collection could contain:
10
10
20A better design is to maintain unique department IDs using an associative array.
CREATE OR REPLACE TRIGGER trg_salary_check
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
TYPE t_dept_set IS TABLE OF BOOLEAN
INDEX BY employees.department_id%TYPE;
g_depts t_dept_set;
AFTER EACH ROW IS
BEGIN
g_depts(:NEW.department_id) := TRUE;
END AFTER EACH ROW;
AFTER STATEMENT IS
v_dept_id employees.department_id%TYPE;
v_total NUMBER;
BEGIN
v_dept_id := g_depts.FIRST;
WHILE v_dept_id IS NOT NULL LOOP
SELECT SUM(salary)
INTO v_total
FROM employees
WHERE department_id = v_dept_id;
DBMS_OUTPUT.PUT_LINE(
'Department ' || v_dept_id ||
' = ' || v_total
);
v_dept_id := g_depts.NEXT(v_dept_id);
END LOOP;
END AFTER STATEMENT;
END trg_salary_check;
/Now each department is processed only once.
28. What are the main advantages of compound triggers?
1. Avoid mutating-table problems
AFTER EACH ROW
↓
Collect data
↓
AFTER STATEMENT
↓
Query table2. Share state
Row section
↓
Collection
↓
Statement section3. Reduce the number of related triggers
Instead of maintaining:
BEFORE STATEMENT trigger
BEFORE ROW trigger
AFTER ROW trigger
AFTER STATEMENT triggerrelated logic can be combined into:
ONE COMPOUND TRIGGER4. Improve bulk-processing designs
Instead of performing expensive processing for every row:
1,000 rows
↓
1,000 expensive operationsYou can collect the information and process it after the statement:
1,000 rows
↓
Collect information
↓
Process after statement29. What are common compound-trigger interview traps?
Trap 1: Is a compound trigger only row-level?
Answer: ❌ No.
It can contain both statement-level and row-level sections.
Trap 2: Can :NEW be used in AFTER STATEMENT?
Answer: ❌ No.
Use :NEW and :OLD in row-level sections.
Trap 3: Can we modify :NEW in AFTER EACH ROW?
Answer: ❌ No.
Modification of :NEW is done in a BEFORE EACH ROW section.
Trap 4: Does a compound trigger automatically solve every mutating-table problem?
Answer: ❌ No.
It provides a mechanism to move processing from the row-level phase to the statement-level phase, but the trigger still needs to be designed correctly.
Trap 5: Does compound-trigger state survive between SQL statements?
Answer: ❌ No.
The shared state belongs to the triggering statement.
Trap 6: Can a compound trigger commit?
Answer: ❌ No.
Normal transaction-control restrictions on triggers still apply.
30. Compound trigger vs normal trigger
| Feature | Normal Trigger | Compound Trigger |
|---|---|---|
| Multiple timing sections | ❌ | ✅ |
| BEFORE STATEMENT | Separate trigger | ✅ |
| BEFORE EACH ROW | Separate trigger | ✅ |
| AFTER EACH ROW | Separate trigger | ✅ |
| AFTER STATEMENT | Separate trigger | ✅ |
| Shared statement-duration state | Limited | ✅ |
| Mutating-table solutions | Sometimes | ⭐ Excellent use case |
:OLD / :NEW |
Row-level only | Row-level sections |
COMMIT |
❌ | ❌ |
31. Compound trigger vs row-level trigger
Row-level trigger:
FOR EACH ROW
↓
Process one row
↓
Process next row
↓
Process next rowCompound trigger:
COMPOUND TRIGGER
↓
BEFORE STATEMENT
↓
BEFORE EACH ROW
↓
AFTER EACH ROW
↓
AFTER STATEMENTThe compound trigger gives you shared state and multiple timing points.
32. ⭐ Compound Trigger Interview Cheat Sheet
| Concept | Answer |
|---|---|
| Multiple timing sections | ✅ |
BEFORE STATEMENT |
✅ |
BEFORE EACH ROW |
✅ |
AFTER EACH ROW |
✅ |
AFTER STATEMENT |
✅ |
:OLD / :NEW |
Row sections only |
Modify :NEW |
BEFORE EACH ROW |
| Shared variables | ✅ |
| State duration | Triggering statement |
| Mutating-table solution | ✅ Common use |
COMMIT |
❌ |
The Most Important Pattern
COMPOUND TRIGGER
|
+-----------+-----------+
| |
EACH ROW STATEMENT
| |
↓ ↓
Collect information Process information
| |
+----------→------------+
|
Shared stateThe Easiest Way to Remember
Normal row trigger: "Process this row."
Statement trigger: "Process this SQL statement."
Compound trigger: "Collect information while processing rows, then use it when the statement is complete."
🎯 Three Most Important Interview Concepts
- Multiple timing sections
- Shared statement-duration state
- Solving mutating-table problems
If the interviewer asks, "Why would you use a compound trigger?", a strong answer is:
"I would use a compound trigger when I need to share state between row-level and statement-level processing, especially when I need to collect row information and then process or query the table after the statement completes to avoid a mutating-table problem."
No comments:
Post a Comment