Triggers in PL/SQL

Triggers are PL/SQL blocks stored in the database that execute automatically before, after, or instead of a specific event. They help in performing actions like validation, logging, and enforcing business rules.

Why Do We Use Triggers?

• To execute pre-tests and post-tests on DML and DDL operations.

• To enforce business rules beyond constraints like NOT NULL, PRIMARY KEY, and CHECK.

• To perform actions before or after DML (Insert, Update, Delete) and DDL (Create, Alter, Drop) operations.

• To log changes in the database (e.g., keeping track of modifications).

• To ensure data integrity by allowing/rejecting changes based on complex conditions.

• To automate actions such as copying data or handling security-related events (logon, logoff, etc.).

Where Can We Define Triggers?

Triggers can be created on:

• Tables

• Views

• Schemas

• The entire database

When Are Triggers Fired?

Triggers execute automatically when specific events occur:

1. DML Operations – INSERT, UPDATE, DELETE

2. DDL Operations – CREATE, ALTER, DROP

3. Database Events – LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVER ERROR

4. Application Events – Used within Oracle applications like Oracle Forms Developer.

Types of Triggers

A. DML Triggers (Data Manipulation Language)

• BEFORE Triggers – Execute before a DML operation.

• AFTER Triggers – Execute after a DML operation.

• INSTEAD OF Triggers – Used for views to override standard DML behavior.

Note: Only one timing option can be selected per trigger.

B. Non-DML Triggers

• DDL Event Triggers – Fire on schema changes (CREATE, DROP, ALTER).

• Database Event Triggers – Fire on system events (LOGON, LOGOFF, SHUTDOWN).

C. Compound Triggers

• Allow defining multiple timing points (BEFORE, AFTER, INSTEAD OF) in a single trigger.

DML Triggers

Triggers are created using the CREATE OR REPLACE TRIGGER statement.

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE | AFTER | INSTEAD OF
  INSERT OR UPDATE OR DELETE
  ON table_name
  REFERENCING OLD AS old_alias NEW AS new_alias
  FOR EACH ROW  -- (Optional: For row-level triggers)
  WHEN (condition)  -- (Optional: Additional condition)
BEGIN
  -- Trigger logic here
END;

Events That Fire Triggers

Triggers execute based on the following events:

• INSERT: Fires when a new record is inserted.

• UPDATE: Fires when a record is updated.

• DELETE: Fires when a record is deleted.

• UPDATE OF column_name: Fires only when a specific column is updated.

Multiple events can be combined using OR, e.g., INSERT OR UPDATE OR DELETE .However, UPDATE and UPDATE OF cannot be used together.

Row-Level vs. Statement-Level Triggers

• Row-Level Triggers (FOR EACH ROW): Executes once for each affected row. Can access both old and new values using OLD and NEW qualifiers.

• Statement-Level Triggers (default behavior): Executes once per SQL statement, regardless of the number of affected rows. Even if no rows are affected, the trigger will still fire. Do not access individual row data.

• Execution order:

  1. BEFORE Statement Trigger

  2. BEFORE ROW Trigger (for each row)

  3. DML Operation (for each row)

  4. AFTER ROW Trigger (for each row)

  5. AFTER Statement Trigger

OLD and NEW Qualifiers

• Used to reference values before and after the DML event.

• :OLD.column_name holds the value before the change.

• :NEW.column_name holds the value after the change.

• In the WHEN condition of a trigger, the colon (:) prefix is not required for OLD and NEW qualifiers.

Behavior of OLD and NEW in Different DML Operations

Using Conditional Predicates in Triggers

Conditional predicates are built-in expressions that determine which DML operation triggered the event. Basically use to write different logic for insert , delete and update.

• INSERTING – Evaluates to TRUE if an INSERT operation occurred.

• UPDATING – Evaluates to TRUE if an UPDATE operation occurred.

• DELETING – Evaluates to TRUE if a DELETE operation occurred.

• UPDATING('column_name') - Evaluates to TRUE if an UPDATE operation occurred on specific column.

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE | AFTER | INSTEAD OF
  INSERT OR UPDATE OR DELETE
  ON table_name
  REFERENCING OLD AS old_alias NEW AS new_alias
  FOR EACH ROW  -- (Optional: For row-level triggers)
  WHEN (condition)  -- (Optional: Additional condition)
BEGIN
    IF INSERTING THEN
        -- Action for INSERT operation
    ELSIF UPDATING('column') THEN
        -- Action for UPDATE operation
    ELSIF UPDATING THEN
        -- Action for UPDATE operation
    ELSIF DELETING THEN
        -- Action for DELETE operation
    END IF;
END;

Using RAISE_APPLICATION_ERROR in Triggers

• A built-in PL/SQL procedure that raises an error and causes the code execution to fail.

• Used in triggers to prevent invalid data changes.

• When executed, it raises an exception, automatically rolling back the triggering statement.

IF :NEW.salary > 50000 THEN
   RAISE_APPLICATION_ERROR(-20002, 'A salary cannot be higher than 50000.');
END IF;

Using the UPDATE OF Event Type in Triggers

• A trigger event type that fires only when specific columns are updated.

• Improves performance by preventing unnecessary executions of triggers.

• Eliminates the need for multiple IF conditions inside the trigger.

CREATE OR REPLACE TRIGGER prevent_updates_of_constant_columns  
BEFORE UPDATE OF hire_date , salary  
ON employees_copy  
FOR EACH ROW  
BEGIN  
   RAISE_APPLICATION_ERROR(-20003, 'You cannot modify the hire_date!.');  
END;

INSTEAD OF Triggers

• Used to perform DML (INSERT, UPDATE, DELETE) operations on unupdatable (complex) views.

• Simple views allow DML operations, but complex views (with JOIN, GROUP BY, functions, etc.) do not.

• How It Works:

  • The trigger fires instead of the DML event.

  • Instead of executing a direct INSERT/UPDATE/DELETE on a complex view, the trigger performs equivalent operations on the underlying tables.

Example :

1. Create a Complex View

CREATE OR REPLACE VIEW vw_emp_details AS
SELECT 
    UPPER(department_name) AS dname, 
    MIN(salary) AS min_sal, 
    MAX(salary) AS max_sal
FROM employees_copy 
JOIN departments_copy USING (department_id)
GROUP BY department_name;

2. Try a DML Operation on the View

UPDATE vw_emp_details 
SET dname = 'EXEC DEPT' 
WHERE dname = 'EXECUTIVE';

Error Message: Data manipulation operation not legal for this view.

3. Create an INSTEAD OF Trigger

CREATE OR REPLACE TRIGGER emp_details_vw_dml
INSTEAD OF INSERT OR UPDATE OR DELETE ON vw_emp_details
FOR EACH ROW 
DECLARE 
    v_dept_id PLS_INTEGER;
BEGIN 
    -- Handle INSERT operation
    IF INSERTING THEN 
        SELECT MAX(department_id) + 10 INTO v_dept_id FROM departments_copy;
        INSERT INTO departments_copy VALUES (v_dept_id, :NEW.dname, NULL, NULL);

    -- Handle DELETE operation
    ELSIF DELETING THEN 
        DELETE FROM departments_copy WHERE UPPER(department_name) = UPPER(:OLD.dname);

    -- Handle UPDATE operation (only allowing department name change)
    ELSIF UPDATING('DNAME') THEN
        UPDATE departments_copy 
        SET department_name = :NEW.dname
        WHERE UPPER(department_name) = UPPER(:OLD.dname);

    -- Prevent updates to other columns
    ELSE 
        RAISE_APPLICATION_ERROR(-20001, 'You cannot update any data other than department name!');
    END IF;
END;

Trigger States

A trigger in Oracle can have two primary states:

1. Valid – The trigger is correctly structured and can execute without errors.

2. Invalid – The trigger has issues preventing its execution, which can block DML (Data Manipulation Language) operations on the associated table or view.

Additionally, a trigger can be:

1. Enabled – The trigger is active and executes when the defined event occurs.

2. Disabled – The trigger is inactive and does not execute, but it does not interfere with user DML operations.

Why Create Disabled Triggers?

Creating a trigger as disabled can be useful in the following scenarios:

• To prevent errors during the initial creation of a potentially invalid trigger.

• To allow users to continue performing DML operations even if the trigger is invalid.

• To temporarily disable business logic while debugging or migrating data.

• To improve performance during bulk data loads.

Creating a Disabled Trigger

Starting from Oracle 11g, you can create triggers in a disabled state using the DISABLE keyword. The syntax is as follows:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
DISABLE
BEGIN
   -- Trigger logic here
END;

Verifying the Trigger State

After creating a trigger, you can check its status in SQL Developer or by querying the data dictionary:

SELECT TRIGGER_NAME, STATUS
FROM USER_TRIGGERS
WHERE TRIGGER_NAME = 'trigger_name';

• When a trigger is created as disabled, the DISABLE keyword is not stored in its definition.

• If you recompile the trigger, it is automatically enabled unless explicitly disabled again.

• The DISABLE command is a one-time directive; once the trigger is compiled, the command does not persist.

• To enable a trigger manually, use: ALTER TRIGGER trigger_name ENABLE;

• To disable a trigger manually, use: ALTER TRIGGER trigger_name DISABLE;

Compound Triggers

• A compound trigger is a single trigger that allows defining actions for multiple timing points (BEFORE and AFTER STATEMENT/ROW levels).

• Introduced in Oracle Database 11g to improve performance and facilitate shared variables among trigger sections.

Benefits of Compound Triggers

• Enables the sharing of variables and types across trigger sections.

• Reduces performance issues associated with multiple independent triggers.

• Avoids mutating table errors.

• Allows efficient bulk processing using BULK INSERTs.

Structure of a Compound Trigger

A compound trigger consists of five sections:

1. Declaration Section – Defines global variables, types, and collections.

2. BEFORE STATEMENT Section – Executes before the DML statement.

3. BEFORE EACH ROW Section – Executes before each row is affected.

4. AFTER EACH ROW Section – Executes after each row is affected.

5. AFTER STATEMENT Section – Executes after the entire DML statement.

The statement sections are optional, but at least one must be defined.

Restrictions on Compound Triggers

• Must be a DML trigger (cannot be a DDL trigger).

• Can be created on tables or views only.

• Cannot contain anonymous PL/SQL blocks or procedures in sections.

• Cannot have an initialization block.

• Each section must handle its own exceptions separately.

• :OLD and :NEW qualifiers can only be used in BEFORE EACH ROW and AFTER EACH ROW sections.

CREATE OR REPLACE TRIGGER trg_comp_example
FOR INSERT OR UPDATE OR DELETE ON employees_copy
COMPOUND TRIGGER

-- Declaration Section
v_dml_type VARCHAR2(10);

BEFORE STATEMENT IS
BEGIN
    IF INSERTING THEN v_dml_type := 'INSERT';
    ELSIF UPDATING THEN v_dml_type := 'UPDATE';
    ELSIF DELETING THEN v_dml_type := 'DELETE';
    END IF;
    DBMS_OUTPUT.PUT_LINE('Before Statement Section executed for ' || v_dml_type || ' event');
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before Row Section executed for ' || v_dml_type || ' event');
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('After Row Section executed for ' || v_dml_type || ' event');
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('After Statement Section executed for ' || v_dml_type || ' event');
END AFTER STATEMENT;

END trg_comp_example;

Mutating table errors

Mutating table errors occur when a trigger tries to query or modify the same table that is currently being modified. This issue arises in specific types of triggers:

• Row-level triggers (e.g., BEFORE EACH ROW or AFTER EACH ROW).

• Statement-level triggers only if fired due to ON DELETE CASCADE constraints.

Why Do Mutating Table Errors Occur?

Oracle Database enforces this restriction to prevent data inconsistencies. Since a table is actively being modified, querying or modifying it within a trigger can lead to unpredictable results.

For example, assume we need to restrict high salaries based on department averages. If we attempt to calculate the average salary using a BEFORE EACH ROW or AFTER EACH ROW trigger, Oracle prevents the query from executing within the same table, resulting in a mutating table error.

Understanding Mutating Tables

A table is considered mutating if:

• It is currently being modified by INSERT, UPDATE, or DELETE.

• It is being updated due to a DELETE CASCADE from another table.

• A row-level trigger tries to query or modify it.

Since statement-level triggers execute before or after changes occur, they do not encounter mutating table errors (except in the case of DELETE CASCADE).

Solutions to Mutating Table Errors

Several approaches can be used to handle mutating table errors:

1. Store Related Data in Another Table

   • Instead of querying the same table, store necessary data in a different table.

2. Store Related Data in a Package

   • Use PL/SQL packages to store and retrieve data within BEFORE STATEMENT triggers.

   • This is helpful when dealing with statement-level triggers and ON DELETE CASCADE.

3. Using Compound Triggers (Preferred Method)

   • Oracle introduced compound triggers to allow using all trigger timing points within a single trigger.

   • Compound triggers solve mutating table errors by separating query operations from row-level modifications.

Example: Mutating Table Error

Consider the following trigger that results in a mutating table error:

CREATE OR REPLACE TRIGGER TRG_MUTATING_EMPS
BEFORE INSERT OR UPDATE ON EMPLOYEES_COPY
FOR EACH ROW
DECLARE
    V_AVG_SALARY NUMBER;
BEGIN
    SELECT AVG(SALARY)
    INTO V_AVG_SALARY
    FROM EMPLOYEES_COPY
    WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

    IF :NEW.SALARY > V_AVG_SALARY * 1.15 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary increase exceeds 15% of department average');
    END IF;
END;

Executing an update statement on EMPLOYEES_COPY triggers a mutating table error.

Using Compound Trigger to Solve the Issue

A compound trigger consists of multiple timing sections (BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, AFTER STATEMENT).

Solution Using Compound Trigger

CREATE OR REPLACE TRIGGER TRG_COMPOUND_EMPLOYEES
FOR INSERT OR UPDATE ON EMPLOYEES_COPY
COMPOUND TRIGGER
    TYPE T_DEPT_AVG_SALARY IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    V_AVG_SALARIES T_DEPT_AVG_SALARY;

    BEFORE STATEMENT IS
    BEGIN
        SELECT DEPARTMENT_ID, AVG(SALARY)
        BULK COLLECT INTO V_AVG_SALARIES
        FROM EMPLOYEES_COPY
        GROUP BY DEPARTMENT_ID;
    END BEFORE STATEMENT;

    BEFORE EACH ROW IS
    BEGIN
        IF :NEW.SALARY > V_AVG_SALARIES(:NEW.DEPARTMENT_ID) * 1.15 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salary increase exceeds 15% of department average');
        END IF;
    END BEFORE EACH ROW;

END TRG_COMPOUND_EMPLOYEES;