DEV Community

Pranav Bakare
Pranav Bakare

Posted on

1 1 1 1 1

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;
Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay