Compound Triggers Notes

Compound triggers are a special type of trigger in Oracle that allow you to combine multiple timing points and events (such as BEFORE and AFTER, INSERT, UPDATE, and DELETE) within a single trigger body. This feature helps to avoid common issues like mutating table errors and trigger recursion while providing more efficient and cleaner ways to write triggers, especially for complex data manipulation operations.

Key Concepts of Compound Triggers

1.     What is a Compound Trigger?

o   A compound trigger is a single trigger that can handle multiple events or timing points within one trigger definition. It provides a way to define multiple actions for different events (INSERT, UPDATE, DELETE) and timing points (BEFORE, AFTER, INSTEAD OF) in a single trigger body.

o   The main advantage of compound triggers is that they allow different actions to be defined for the same event but in different timing points, avoiding the common problem of mutating tables and nested triggers.

2.     Syntax of a Compound Trigger

The basic syntax of a compound trigger is as follows:

CREATE OR REPLACE TRIGGER trigger_name
FOR INSERT, UPDATE, DELETE ON table_name
COMPOUND TRIGGER
   -- Declare variables or constants here
   -- Optional: declare shared variables for row-level logic
 
   -- BEFORE statement-level logic
   BEFORE STATEMENT IS
   BEGIN
      -- Before statement-level code (optional)
   END BEFORE STATEMENT;
 
   -- AFTER statement-level logic
   AFTER STATEMENT IS
   BEGIN
      -- After statement-level code (optional)
   END AFTER STATEMENT;
 
   -- BEFORE row-level logic
   BEFORE EACH ROW IS
   BEGIN
      -- Before row-level code (optional)
   END BEFORE EACH ROW;
 
   -- AFTER row-level logic
   AFTER EACH ROW IS
   BEGIN
      -- After row-level code (optional)
   END AFTER EACH ROW;
 
END trigger_name;
/

o   FOR: Specifies the events (e.g., INSERT, UPDATE, DELETE) and the table on which the trigger is defined.

o   COMPOUND TRIGGER: Indicates that the trigger will contain multiple timing points.

o   BEFORE / AFTER: Defines when the trigger logic should execute relative to the DML operation (before or after).

o   STATEMENT / ROW: Defines whether the logic is statement-level (executed once per DML statement) or row-level (executed once per row affected by the DML operation).

3.     Components of a Compound Trigger A compound trigger allows you to define four different types of timing and event combinations:

o   BEFORE STATEMENT: Executes before the DML operation starts, at the statement level (before any rows are affected).

o   AFTER STATEMENT: Executes after the DML operation finishes, at the statement level (after all rows have been affected).

o   BEFORE EACH ROW: Executes before the DML operation on each row, for each row affected by the statement.

o   AFTER EACH ROW: Executes after the DML operation on each row, for each row affected by the statement.

4.     Advantages of Compound Triggers

o   Avoid Mutating Table Errors: One of the most common use cases for compound triggers is preventing mutating table errors (errors that occur when a trigger attempts to modify or query the same table that caused the trigger to fire).

o   No Nested Triggers: Unlike traditional triggers that may cause recursive trigger calls, compound triggers execute all logic in a single execution cycle, preventing trigger recursion.

o   Cleaner and More Efficient Code: By consolidating multiple timing points and events into a single trigger, compound triggers reduce code duplication and improve maintainability.

Example of Compound Triggers

1. Example: Tracking Changes in a Table

Suppose we have an employees table, and we want to track changes in employee salary and department. We will use a compound trigger to handle updates to the table and track these changes in an audit_log table.

CREATE OR REPLACE TRIGGER emp_salary_dept_trigger
FOR INSERT, UPDATE, DELETE ON employees
COMPOUND TRIGGER
 
   -- Declare variables to hold old and new salary values
   salary_before_update employees.salary%TYPE;
   salary_after_update employees.salary%TYPE;
 
   -- BEFORE STATEMENT: Perform actions before any row-level processing
   BEFORE STATEMENT IS
   BEGIN
      -- You can use this section to initialize global variables if needed
      NULL;
   END BEFORE STATEMENT;
 
   -- AFTER STATEMENT: Perform actions after all row-level processing
   AFTER STATEMENT IS
   BEGIN
      -- For example, log a summary or send a notification about the DML operation
      NULL;
   END AFTER STATEMENT;
 
   -- BEFORE EACH ROW: Perform actions before the row is modified
   BEFORE EACH ROW IS
   BEGIN
      -- Store the old salary value before an update
      IF UPDATING THEN
         salary_before_update := :OLD.salary;
      END IF;
   END BEFORE EACH ROW;
 
   -- AFTER EACH ROW: Perform actions after the row is modified
   AFTER EACH ROW IS
   BEGIN
      -- Log the change in the audit_log table after salary update
      IF UPDATING THEN
         salary_after_update := :NEW.salary;
         INSERT INTO audit_log (employee_id, action, old_salary, new_salary, action_time)
         VALUES (:NEW.employee_id, 'UPDATE', salary_before_update, salary_after_update, SYSDATE);
      ELSIF DELETING THEN
         INSERT INTO audit_log (employee_id, action, old_salary, new_salary, action_time)
         VALUES (:OLD.employee_id, 'DELETE', :OLD.salary, NULL, SYSDATE);
      END IF;
   END AFTER EACH ROW;
 
END emp_salary_dept_trigger;
/

Explanation:

·        BEFORE STATEMENT: This section can initialize or perform actions before the DML operation starts. In this case, it doesn't perform any action.

·        AFTER STATEMENT: After all rows are processed, you can log or take actions at the statement level. In this case, it's left empty for simplicity.

·        BEFORE EACH ROW: This section captures the old salary value before an update and stores it in the salary_before_update variable.

·        AFTER EACH ROW: After each row is affected by the operation, this section logs changes (for updates or deletions) in the audit_log table.

2. Example: Preventing Invalid Updates

Suppose you want to prevent the salary of employees from being updated to a value greater than $200,000. You can use a compound trigger to check this condition.

CREATE OR REPLACE TRIGGER prevent_salary_update_trigger
FOR UPDATE ON employees
COMPOUND TRIGGER
 
   -- BEFORE EACH ROW: Check the salary before updating
   BEFORE EACH ROW IS
   BEGIN
      IF :NEW.salary > 200000 THEN
         RAISE_APPLICATION_ERROR(-20001, 'Salary cannot exceed $200,000');
      END IF;
   END BEFORE EACH ROW;
 
   -- AFTER EACH ROW: Log the update
   AFTER EACH ROW IS
   BEGIN
      -- Log the update in an audit table (for example purposes)
      INSERT INTO salary_audit (employee_id, old_salary, new_salary, update_time)
      VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
   END AFTER EACH ROW;
 
END prevent_salary_update_trigger;
/

Explanation:

·        BEFORE EACH ROW: The trigger checks if the new salary exceeds $200,000 and raises an exception if the condition is met, preventing the update.

·        AFTER EACH ROW: After the row is updated, the trigger logs the old and new salary values into a salary_audit table.

Key Benefits of Compound Triggers

1.     Avoiding Mutating Table Errors: Compound triggers help to avoid mutating table errors by allowing different types of logic (e.g., BEFORE and AFTER) to be defined in the same trigger. This ensures that data modifications are handled cleanly without interference.

2.     Performance: Since compound triggers consolidate multiple timing points into a single trigger, they can improve performance by reducing the overhead of firing multiple triggers for the same event.

3.     Preventing Trigger Recursion: Compound triggers prevent recursive trigger calls that can occur in traditional triggers, where one trigger firing can cause another to fire, leading to unexpected results or performance degradation.

4.     Better Code Organization: Instead of writing separate triggers for each event or timing point, a compound trigger allows you to group all logic in one place, making it easier to manage and maintain.

Considerations

1.     Complexity: Compound triggers can be more complex than traditional triggers, especially if multiple timing points and events are involved. Be cautious about adding too much logic to a single compound trigger to avoid making it difficult to troubleshoot.

2.     Limitations: While compound triggers are powerful, they can also create potential pitfalls if not properly tested, especially in scenarios where the timing of actions is critical.

3.     Order of Execution: Within the compound trigger, the timing of BEFORE and AFTER actions (for both STATEMENT and ROW levels) is well-defined, but you need to consider the order in which the actions are performed.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)