Skip to content

Trigger

A trigger is something that the system executes automatically when it sees some change in the database. The two key parts of a trigger are:

  • The triggering condition. This can be update based or time based
  • The action to perform when said condition is satisfied
CREATE TRIGGER credits_earned AFTER UPDATE OF takes ON (grade)
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)
END

A trigger can be executed on a statement level instead of a row level too.

Use Cases

  • Maintaining summary data
  • Replicating databases by recording delta relations (VCS basically)

Alternatives

  • Materialised views are good for maintaining summaries
  • Databases provide built-in replication support

Risks

  • Loading data from a backup copy by accident
  • Replicating updates where not needed
  • Cascading execution
  • Error from failure of critical transactions that set off the trigger

Triggers may be disabled before taking such a risk