ISYS1055 Chap.10 SQL Triggers in SQLite: BEFORE, AFTER and INSTEAD OF
SQL Triggers in SQLite: BEFORE, AFTER and INSTEAD OF
Week 8 is entirely triggers. A trigger is a special kind of stored procedure that the engine runs automatically when a specified database event occurs, with no user intervention: the trigger event is a CRUD operation on a table or view, and the trigger action is one or more CRUD statements executed in response. The stated outcomes are to understand what a trigger is and where it is used, the three types (BEFORE, AFTER and INSTEAD OF), the CREATE TRIGGER syntax, and how to use the NEW and OLD values. Triggers complete the integrity story that began with constraints in Week 2 and views in Week 7, and they are the natural way to enforce a rule that no constraint can express - which is why they show up in Part C of the Database Design Project when a business rule refuses to fit into a CHECK or a foreign key.
What this chapter covers
- 01Trigger definition and anatomy: the trigger event (INSERT, UPDATE, optionally UPDATE OF a column, or DELETE on a table or view) and the trigger action
- 02Why triggers exist: enforcing simple business rules the integrity constraints cannot express, and automating housekeeping
- 03Use cases the course names - maintaining a derived or aggregate value, writing an audit or log record, capturing behavioural data, secondary-checkpoint input validation, and making an otherwise non-executable statement work
- 04The three types and their control flow: BEFORE runs the action then the event, AFTER runs the event then the action, INSTEAD OF skips the event and runs the action in its place
- 05Choosing between them: BEFORE when the action depends on the state before the change, AFTER when it depends on the result of the change, INSTEAD OF when the event itself cannot execute
- 06The NEW and OLD pseudo-records, and the availability table - INSERT has NEW only, UPDATE has both, DELETE has OLD only
- 07CREATE TRIGGER syntax in SQLite, including FOR EACH ROW, the optional WHEN condition, and the BEGIN ... END body
- 08INSTEAD OF on a view, and the limitation that a view built on grouped aggregation may have no well-defined mapping back to its base tables
Choose the trigger type and write an audit trigger for a changing rate
- +1(a) The event is an update of one specific column on one table, which SQLite lets you say precisely: UPDATE OF daily_rate ON tool. Narrowing the event to the column matters - a trigger written on any update of tool would fire when a tool is renamed and would write a log row recording no change at all.
- +1(b) The action here is to record the state that is about to be replaced, so it is based on the state prior to the change; that is the course's stated test for choosing BEFORE, and its own audit-log pattern is a BEFORE trigger. Contrast the other case: recomputing a total from the rows that were just changed depends on the result of the change and can only be an AFTER trigger. INSTEAD OF is not in play, because an update on a base table executes perfectly well on its own.
- +1(c) Create the log table, then the trigger, reading both values from the pseudo-records. CREATE TABLE rate_log ( toolID INTEGER NOT NULL, old_rate REAL NOT NULL, new_rate REAL NOT NULL, changed_at TEXT NOT NULL ); then CREATE TRIGGER log_rate_change BEFORE UPDATE OF daily_rate ON tool BEGIN INSERT INTO rate_log VALUES (OLD.toolID, OLD.daily_rate, NEW.daily_rate, datetime('now')); END; - OLD.daily_rate is the value being replaced, NEW.daily_rate the value replacing it, and datetime('now') stamps the row.
- +1(d) Availability follows the event, not the timing. On an INSERT there is no previous version of the row, so only NEW exists; on a DELETE there is no resulting version, so only OLD exists; on an UPDATE both are available. Note also that for UPDATE OF daily_rate, OLD.toolID and NEW.toolID hold the same value, because only the rate is being changed - which is why either can be used for the identifier.
Key terms
- Trigger
- A special type of stored procedure that the database engine executes automatically when a specified event occurs on a table or view, with no user intervention. It exists to enforce business rules that the declarative integrity constraints cannot express, and to automate housekeeping.
- Trigger event and trigger action
- The two parts of every trigger. The event is the CRUD operation being watched - INSERT, UPDATE (optionally UPDATE OF a named column), or DELETE. The action is the statement or statements run in response, themselves one or more CRUD operations.
- BEFORE trigger
- The action runs first and the event follows. Chosen when the action must be based on the state prior to the change, such as writing a log record of the value that is about to be overwritten.
- AFTER trigger
- The event runs first and the action follows. Chosen when the action depends on the result of the change, such as recomputing a total from rows that have just been updated.
- INSTEAD OF trigger
- The event is skipped entirely and the action runs in its place. Used mainly for DML against a view, which cannot execute on its own; the trigger performs the equivalent work on the base tables instead.
- NEW and OLD
- Pseudo-records the engine exposes during trigger execution, needing no declaration. NEW holds the values that will exist after the operation and OLD the values that existed before. INSERT exposes NEW only, DELETE exposes OLD only, and UPDATE exposes both.
SQL Triggers in SQLite: BEFORE, AFTER and INSTEAD OF FAQ
How do I decide between BEFORE and AFTER?
Ask what the action depends on. If it depends on the state before the change - logging the value that is about to be replaced, or checking something about the incoming row - the course's pattern is BEFORE. If it depends on the result of the change - recomputing an aggregate from rows that must already have been updated - it has to be AFTER, because the numbers do not exist yet otherwise. The lectorial's own practice set works exactly this way: for each scenario, decide the type and justify it.
What is an INSTEAD OF trigger actually for?
Making a statement work that could not execute at all. Views hold no data, so an update against a view fails; an INSTEAD OF trigger intercepts that statement and issues an equivalent one against the underlying base tables. It is the standard way of making a view updatable - with the caveat that where a view is defined over aggregated or grouped values, there may be no well-defined mapping from a summary row back to base rows, so no trigger can replicate the change.
Do triggers replace integrity constraints?
No - they extend them. The order of expressive power is constraints first (key, entity and referential integrity, declared and enforced by the model), then views to restrict what is visible, then triggers for procedural rules and automation that constraints cannot express. Anything a constraint can enforce should be a constraint, because it is declarative, cheaper and impossible to forget.
Why does my trigger not fire?
Three common reasons in this course. The event is too broad or too narrow - UPDATE OF one column will not fire when a different column changes. The demonstration data was inconsistent to begin with, so the effect is invisible; the course insists on making the stored totals agree with the source rows before demonstrating an aggregate trigger. Or the body contains a character that is not plain ASCII, copied from a slide, so the statement never compiled in the first place.
Assessment move
Build the three patterns yourself in SQLite Studio rather than reading them - an audit log on a value change, an aggregate kept in step after an update, and an INSTEAD OF trigger that writes through a view - and fire each one with a single statement so you can see the log row or the recomputed total appear. Before you demonstrate an aggregate trigger, make the stored total consistent with the underlying rows, or the demonstration proves nothing. Memorise the two small tables (type versus control flow, event versus NEW and OLD availability) because they are precisely the shape of a short-answer quiz item, and quiz 4 falls in your Week 9 practical covering Weeks 7 and 8. Type every code block by hand: this is the highest copy-paste-failure surface in the course.
Working through SQL Triggers in SQLite: BEFORE, AFTER and INSTEAD OF in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 SQL Triggers in SQLite: BEFORE, AFTER and INSTEAD OF question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
