Triggers

The trigger: event (INSERT, UPDATE, DELETE), timing (BEFORE, AFTER, INSTEAD OF), row or statement level, OLD and NEW, its uses (log, computed value, rule) and its dangers (cascade, hidden logic).

27 cards
6 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
Scenarios
Understand

A few cards from the deck

Trigger: what is it?
Show the answer
Code the database runs by itself, with no explicit call, when an INSERT, UPDATE or DELETE event touches a given table.
It runs whichever program writes: application, script or manual input. That is its strength, and also what makes it easy to overlook.
UPDATE produit SET stock = stock - 3 touches a row where stock is 10. What are OLD.stock and NEW.stock in the row-level trigger?
- 10 et 7
- 7 et 10
- 10 et 3
- 7 et 7
Show the answer
10 et 7
OLD keeps the row before the change (10) and NEW the row as it will be written (10 - 3 = 7). 7 and 10 swaps the roles.
A trigger is defined by an event (…, UPDATE or DELETE), a timing (…, AFTER or INSTEAD OF) and a level.
Show the answer
A trigger is defined by an event (INSERT, UPDATE or DELETE), a timing (BEFORE, AFTER or INSTEAD OF) and a level.
These three choices decide when the code runs, how many times, and with which variables available.
Trigger timing: what is it?
Show the answer
When it runs relative to the write: BEFORE, before it happens, AFTER, once it is done, or INSTEAD OF, in its place.
BEFORE can change or refuse the row; AFTER sees the final result, useful for logging. The choice depends on what the code must change.

Study this deck