Solution to a mutating trigger error in Oracle SQL

To solve a mutating trigger error in Oracle SQL, you need to avoid querying or modifying the triggering table while it is being updated. Here are common solutions:

---

1. Use Compound Triggers (Recommended for Oracle 11g+)

A compound trigger allows you to split the logic into different phases (BEFORE, AFTER, etc.) and handle operations after all rows are processed, avoiding direct interaction with the table during row-level changes.

Example:

CREATE OR REPLACE TRIGGER trg_check_salary
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
TYPE t_employee_ids IS TABLE OF employees.employee_id%TYPE;
employee_ids t_employee_ids := t_employee_ids();

BEFORE STATEMENT IS
BEGIN
employee_ids := t_employee_ids(); -- Initialize collection
END BEFORE STATEMENT;

AFTER EACH ROW IS
BEGIN
employee_ids.EXTEND;
employee_ids(employee_ids.LAST) := :NEW.employee_id; -- Collect IDs
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
-- Process collected IDs after all rows are processed
FOR i IN employee_ids.FIRST .. employee_ids.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Processed Employee ID: ' || employee_ids(i));
END LOOP;
END AFTER STATEMENT;
END;
/

This avoids direct interaction with the table during row-level processing by postponing operations to the AFTER STATEMENT phase.

---

1. Use Temporary Tables

Store intermediate results in a temporary table and process them after the trigger finishes.

Example:

CREATE GLOBAL TEMPORARY TABLE temp_employees (emp_id NUMBER);

CREATE OR REPLACE TRIGGER trg_check_salary
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
-- Insert into temporary table instead of querying the main table
INSERT INTO temp_employees (emp_id) VALUES (:NEW.employee_id);
END;
/
-- Process the temp_employees table later as needed.

---

1. Use Autonomous Transactions

You can use an autonomous transaction to query or modify the table outside the scope of the trigger. This separates the transaction, avoiding the mutating table issue.

Example:

CREATE OR REPLACE TRIGGER trg_check_salary
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- This query runs in a separate transaction
INSERT INTO audit_table (emp_id, change_date)
VALUES (:NEW.employee_id, SYSDATE);
COMMIT;
END;
/

⚠️ Use this cautiously, as it bypasses some constraints and may lead to data inconsistencies if not handled correctly.

---

1. Use Statement-Level Triggers

If you don’t need row-level processing, use a statement-level trigger (BEFORE or AFTER) instead of a row-level trigger (FOR EACH ROW).

Example:

CREATE OR REPLACE TRIGGER trg_check_salary
AFTER INSERT OR UPDATE ON employees
BEGIN
-- Process the table after the entire statement completes
INSERT INTO audit_table (change_summary)
VALUES ('Rows updated in employees table at ' || SYSDATE);
END;
/

---

Summary

Compound Triggers: Best for handling operations in phases (Oracle 11g+).

Temporary Tables: Store intermediate data and process it later.

Autonomous Transactions: Run separate transactions for queries/modifications.

Statement-Level Triggers: Process data after the entire operation completes.

The choice depends on your requirements, but compound triggers are the most robust and maintainable solution.