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