Triggers | Advanced Data Modeling

Introduction

Triggers in Oracle are special stored procedures that are automatically executed or fired when specific events occur. They can be used to enforce business rules, validate input data, and maintain audit trails.

Creating Triggers

To create a trigger in Oracle, you use the CREATE TRIGGER statement. Here is the basic syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} 
{INSERT | UPDATE | DELETE}
[OF column_name]
ON table_name
[FOR EACH ROW]
DECLARE
    -- variable declarations
BEGIN
    -- trigger body
EXCEPTION
    -- exception handling
END;

Let's create a simple trigger that logs insertions into the employees table:

CREATE OR REPLACE TRIGGER log_employee_inserts
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (emp_id, action, log_date)
    VALUES (:NEW.emp_id, 'INSERT', SYSDATE);
END;

Trigger Types

Oracle supports several types of triggers:

Row-level triggers: Fire once for each row affected by the triggering event.
Statement-level triggers: Fire once for each triggering event, regardless of the number of rows affected.
BEFORE triggers: Fire before the triggering event.
AFTER triggers: Fire after the triggering event.
INSTEAD OF triggers: Fire instead of the triggering event (used with views).

Row-Level Trigger Example

CREATE OR REPLACE TRIGGER update_employee_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary decrease is not allowed.');
    END IF;
END;

Statement-Level Trigger Example

CREATE OR REPLACE TRIGGER log_salary_update
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO salary_log (log_date, user_name)
    VALUES (SYSDATE, USER);
END;

INSTEAD OF Trigger Example

INSTEAD OF triggers are typically used with views to enable DML operations. Example:

CREATE OR REPLACE TRIGGER emp_view_insert
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (emp_id, emp_name, salary)
    VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.salary);
END;

Managing Triggers

Disabling and Enabling Triggers

You can disable and enable triggers using the ALTER TRIGGER statement:

ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;

Dropping Triggers

To drop a trigger, use the DROP TRIGGER statement:

DROP TRIGGER trigger_name;

Advanced Topics

Mutating Table Error

The mutating table error occurs when a trigger attempts to read or modify the table that caused the trigger to fire. To avoid this error, you can use compound triggers or temporary tables.

Compound Triggers

Compound triggers allow you to define multiple timing points for a single trigger, reducing the risk of mutating table errors. Example:

CREATE OR REPLACE TRIGGER compound_example
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
  g_emp_tab t_emp_tab := t_emp_tab();

  BEFORE STATEMENT IS
  BEGIN
    g_emp_tab.DELETE;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    g_emp_tab.EXTEND;
    g_emp_tab(g_emp_tab.LAST) := :NEW;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1..g_emp_tab.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Updated salary for employee: ' || g_emp_tab(i).emp_name);
    END LOOP;
  END AFTER STATEMENT;

END compound_example;

Conclusion

Triggers in Oracle are a powerful mechanism for enforcing business rules, maintaining data integrity, and automating complex operations. By understanding and effectively utilizing different types of triggers, you can significantly enhance the functionality and reliability of your Oracle database applications.

Advanced Data Modeling: Triggers in Oracle