Introduction to Triggers

 Oracle triggers are a powerful feature in the Oracle Database used to automatically perform specific actions when certain events occur in the database. These actions can include things like auditing data changes, enforcing business rules, or generating notifications. Here’s a detailed breakdown of Oracle triggers, including their types, syntax, and use cases:

1. What is a Trigger?

A trigger in Oracle is a stored PL/SQL block that is automatically executed (or "fired") when a specified event occurs on a table or view. The event might be an insert, update, or delete operation. Triggers can be used to enforce business logic, maintain referential integrity, or automate repetitive tasks.

2. Trigger Types

There are several types of triggers in Oracle, classified based on the event that triggers them and the timing of when they should be executed.

a. Based on Events

• DML Triggers (Data Manipulation Language): These are triggered by insert, update, or delete operations on a table or view.
• BEFORE Trigger: Fired before the DML operation is executed.
• AFTER Trigger: Fired after the DML operation is executed.
• INSTEAD OF Trigger: Fired in place of the DML operation. Typically used with views.
• DDL Triggers (Data Definition Language): These are fired in response to changes in the schema (like CREATE, ALTER, or DROP commands).
• LOGON/LOGOFF Triggers: Fired when a user logs in or logs off the database.
• Compound Triggers: A special kind of trigger in Oracle that allows multiple trigger actions (before and after) to be handled in one unit, which can help avoid mutating table errors.

b. Based on Timing

• BEFORE Trigger: Executes before the triggering DML action.
• AFTER Trigger: Executes after the triggering DML action.
• INSTEAD OF Trigger: Replaces the DML action with the code inside the trigger.

3. Trigger Syntax

The basic syntax of creating a trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name

    {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}

    ON table_name

    [FOR EACH ROW]

    [WHEN (condition)]

BEGIN

    -- PL/SQL code

END;

Explanation of Parameters:

• trigger_name: Name of the trigger.
• BEFORE, AFTER, INSTEAD OF: Specifies when the trigger will fire (before or after the DML operation).
• INSERT | UPDATE | DELETE: Specifies which DML operation will activate the trigger.
• ON table_name: Specifies the table or view that the trigger is associated with.
• FOR EACH ROW: A row-level trigger, which is fired once for each row affected by the DML operation.
• WHEN (condition): Optional. Specifies a condition that must be true for the trigger to fire.

 

4. Example Triggers

a. BEFORE Insert Trigger Example

This trigger will ensure that no negative salaries are inserted into the employees table.

CREATE OR REPLACE TRIGGER check_salary_before_insert

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

    IF :NEW.salary < 0 THEN

        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');

    END IF;

END;

• Explanation:
• :NEW.salary refers to the salary value being inserted.
• If the salary is less than 0, it raises an application error.

b. AFTER Update Trigger Example

This trigger updates an audit table after any update to the employees table.

CREATE OR REPLACE TRIGGER audit_employee_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

    INSERT INTO employee_audit (emp_id, old_salary, new_salary, change_date)

    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);

END;

• Explanation:
• :OLD.salary refers to the salary value before the update.
• :NEW.salary refers to the salary value after the update.
• After each update, an entry is inserted into the employee_audit table.

c. INSTEAD OF Trigger Example

This type of trigger is typically used on views.

CREATE OR REPLACE TRIGGER update_employee_view

INSTEAD OF UPDATE ON employee_view

FOR EACH ROW

BEGIN

    UPDATE employees

    SET salary = :NEW.salary

    WHERE employee_id = :OLD.employee_id;

END;

• Explanation:
• This trigger is used to update a view instead of directly modifying the view. It actually updates the underlying employees table when an update is made to the view.

 

5. Trigger Context Variables

Oracle triggers have special context variables that allow you to refer to the values of columns before and after DML operations.

• :NEW: Refers to the new value of a column during INSERT or UPDATE operations.
• :OLD: Refers to the old value of a column during UPDATE or DELETE operations.
• :NEW.column_name: New value for the column column_name (available in INSERT and UPDATE triggers).
• :OLD.column_name: Old value for the column column_name (available in UPDATE and DELETE triggers).

 

6. Row-Level vs Statement-Level Triggers

• Row-level triggers: These are fired for each row affected by the DML operation. They are specified with FOR EACH ROW.

Example:

CREATE OR REPLACE TRIGGER update_salary

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

    IF :NEW.salary > 100000 THEN

        INSERT INTO salary_log (employee_id, action)

        VALUES (:NEW.employee_id, 'Salary increased above threshold');

    END IF;

END;

• Statement-level triggers: These are fired once per DML statement, regardless of how many rows are affected. They are created without the FOR EACH ROW clause.

Example:

CREATE OR REPLACE TRIGGER statement_trigger

AFTER DELETE ON employees

BEGIN

    INSERT INTO audit_log (action, description)

    VALUES ('DELETE', 'An employee record was deleted.');

END;

 

7. Trigger Restrictions

• Mutating table errors: A mutating table error occurs when a trigger attempts to modify a table that is being affected by the DML operation that fired the trigger. To avoid this, you can use compound triggers or temporary tables.
• Triggers cannot call another trigger directly: While triggers can call stored procedures or functions, direct calls to other triggers are not allowed.

 

8. Managing Triggers

• Disable/Enable Triggers:
• To disable a trigger:

o   ALTER TRIGGER trigger_name DISABLE;

• To enable a trigger:

o   ALTER TRIGGER trigger_name ENABLE;

• Drop a Trigger:

·        DROP TRIGGER trigger_name;

 

9. Use Cases for Triggers

• Data validation: Ensuring data integrity by validating data before or after changes.
• Audit logging: Automatically recording data changes for auditing purposes.
• Enforcing business rules: Automatically enforcing business logic without requiring additional application code.
• Enforcing referential integrity: For example, cascading changes when foreign keys are involved.
• Preventing invalid transactions: For instance, preventing negative stock values in an inventory system.

 

10. Advantages and Disadvantages of Triggers

Advantages:

• Automatic execution: Triggers are fired automatically based on events, which reduces manual intervention.
• Centralized business logic: Triggers ensure that certain rules are enforced consistently across all applications that interact with the database.
• Reduced code duplication: Triggers help reduce the need for repetitive checks and validations in application code.

Disadvantages:

• Complexity: Triggers can make the system difficult to troubleshoot, especially when debugging issues related to data changes.
• Performance overhead: Each trigger adds a layer of overhead to DML operations, which could potentially degrade performance if not used carefully.
• Unintended side effects: If triggers are not carefully designed, they can introduce unintended consequences (such as infinite loops if triggers call other triggers).

 

Conclusion

Triggers in Oracle are a powerful feature for automating actions, enforcing business rules, and maintaining data integrity. However, they should be used judiciously to avoid performance issues or unexpected side effects. Understanding the different types of triggers, their syntax, and their use cases will allow you to effectively leverage triggers in your Oracle database applications.