Forem

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more