🌊

Triggers in SQL

Oct 19, 20223 min read

A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.

The two requirements of a trigger design are:

  • Specify the conditions under which the trigger is to be execute.
  • Specify the actions to be taken when the trigger executes.

Triggering events and actions in SQL

  • Triggering event can be an insert, a deletion, or an update.

  • Triggers on updates can be restricted to specific attributes.

  • Values of attributes before and after an update can be referenced.

    • REFERENCING OLD ROW AS: For deletions and updates
    • REFERENCING NEW ROW AS: For insertions and updates

  • Triggers can be activated before and after an event.

    • BEFORE UPDATE OF: Serves as an extra constraint.
    • AFTER UPDATE OF

Examples

  • When the grade attribute is updated for a tuple in the takes relation, keep the tot_cred attribute value of student tuples up-to-date
-- When (Trigger Conditions)
CREATE TRIGGER credits_earned
AFTER UPDATE OF takes ON (grade)
-- What (Trigger Actions)
REFERENCING NEW ROW AS nrow
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN    (nrow.grade <> 'F' AND nrow.grade IS NOT NULL)
AND     (orow.grade = 'F'  OR  orow.grade IS NULL)
BEGIN ATOMIC
    UPDATE  student
    SET     tot_cred = tot_cred +
    (
        SELECT  credits
        FROM    course
        WHERE   course.course_id = nrow.course_id
    )
    WHERE   student.id = nrow.id;
END;

Statement level triggers

Instead of executing a separate action for each affected row, a single action can be executed for all rows affected by a transaction.

  • Instead of FOR EACH ROW, use FOR EACH STATEMENT.

  • Instead of REFERENCING [OLD/NEW] ROW AS ..., use REFERENCING [OLD/NEW] TABLE AS ... to refer to temporary tables (called transition tables).

Statement level triggers can be more efficient when dealing with SQL statements that update a large number of rows.

When not to use triggers

Earlier, triggers were used for tasks such as

  • Maintaining summary data.

  • Replicating databases by recording changes to special relations (called change or delta relations), and having a separate process that applies the changes over to a replica

However, there are better ways of doing these nowadays.

  • Databases today provide built-in materialised view facilities to maintain summary data.

  • Databases provide built-in support for replication.

  • Encapsulation facilities can be used instead of triggers in many cases. It carries out actions as part of the update methods instead of through a trigger.

Risks of using triggers

There are some risks for using triggers. For example,

  • Risk of unintended execution of triggers.

  • Error leading to failure of critical transactions that set off the trigger.

  • Cascading execution of triggers.

Graph View

Backlinks