Trigger Complete Overview in Context of ORACLE SQL

In SQL databases, a trigger is a stored procedure that is automatically executed or fired when certain events occur in a table or a view. Triggers are used to enforce business rules, maintain audit trails, or automatically update dependent data.

Here’s a detailed definition of a trigger and its types based on levels:

Trigger Definition

A trigger is a database object associated with a table or view that is invoked automatically when a specified data modification event (such as INSERT, UPDATE, or DELETE) occurs. Triggers can be defined to:

• Validate data before it is inserted or updated.
• Enforce referential integrity.
• Automatically log changes to another table.
• Synchronize tables or audit actions.

Trigger Types Based on Levels

1. Statement-Level Triggers

• These triggers fire once for the entire SQL statement, regardless of the number of rows affected.
• Typically used for tasks that do not depend on individual row data, such as maintaining logs or enforcing rules at a high level.

Example:

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
BEGIN
    INSERT INTO audit_log(action, action_time)
    VALUES ('Insert operation', SYSDATE);
END;

2. Row-Level Triggers

• These triggers fire once for each row affected by the SQL statement.
• Useful for operations that depend on the specific data in each row.

Example:

CREATE TRIGGER trg_before_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log(employee_id, old_salary, new_salary)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary);
END;

Trigger Types Based on Timing

1. BEFORE Trigger

• Fires before the triggering SQL statement is executed.
• Commonly used for validation or ensuring data integrity before changes occur.

Example: Prevent negative salaries:

CREATE TRIGGER trg_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;

2. AFTER Trigger

• Fires after the triggering SQL statement is executed.
• Often used for tasks like logging or synchronizing changes.

Example: Log deleted rows:

CREATE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees_log(employee_id, deleted_time)
    VALUES (:OLD.employee_id, SYSDATE);
END;

3. INSTEAD OF Trigger

• Used on views to define what action should be taken instead of the default INSERT, UPDATE, or DELETE.
• Commonly used when views are not inherently updatable.

Example:

CREATE TRIGGER trg_instead_of_update
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
BEGIN
    UPDATE employees
    SET name = :NEW.name
    WHERE employee_id = :OLD.employee_id;
END;