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