Theory
Who changed Riya's marks?
Monday: Riya's DBMS score reads 78. Thursday: it reads 88. The exam cell asks ResultDesk: who changed it, when, and from what?
Your tables store the current value, not the story behind it. You could beg every program that touches marks to also write a log... and the day one forgets, the audit trail has a hole.
Better: teach the database itself to react. "Whenever anyone updates marks, record the old and new value, automatically." That standing instruction is a trigger.
Theory
The CCTV above the register
A clerk can promise to note every correction, but promises fail. A CCTV camera over the register does not: it records every change, whoever makes it, even at 2 am.
A trigger is CCTV for a table: parked on the table itself, watching for a chosen event (INSERT, UPDATE, DELETE), and running its recording routine automatically. No application can forget it, because no application is asked.
Theory
Trigger, formally
A trigger is a named database object holding SQL that executes automatically when a specified event occurs on a specified table.
Three choices define one:
- Event: INSERT, UPDATE or DELETE.
- Timing: BEFORE the change (validate, block bad data) or AFTER it (log, cascade).
- Body: the SQL between BEGIN and END.
Inside the body live two magic row-aliases: NEW (the incoming values) and OLD (the values being replaced). INSERT has only NEW; DELETE only OLD; UPDATE has both.
Practical
The audit CCTV, installed
CREATE TABLE marks_log (
roll INTEGER,
subject TEXT,
old_score INTEGER,
new_score INTEGER,
changed_on TEXT
);
CREATE TRIGGER log_marks_change
AFTER UPDATE ON marks
BEGIN
INSERT INTO marks_log
VALUES (OLD.roll, OLD.subject, OLD.score, NEW.score,
datetime('now'));
END;
-- now ANY update writes its own evidence:
UPDATE marks SET score = 88 WHERE roll = 101 AND subject = 'DBMS';
-- marks_log gains: 101, DBMS, 78, 88, 2026-07-04 ...
-- a BEFORE trigger can BLOCK bad data:
CREATE TRIGGER stop_bad_scores
BEFORE UPDATE ON marks
WHEN NEW.score > 100 OR NEW.score < 0
BEGIN
SELECT RAISE(ABORT, 'score must be 0..100');
END;
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Think first
BEFORE or AFTER? Choose per job
Two jobs: (1) refuse any DELETE on the students table during results week; (2) whenever a new mark is INSERTed, update the subject's running total. Before tapping: pick the timing (BEFORE/AFTER) and event for each, and say which of NEW/OLD each body can use.
Show the answer
(1) BEFORE DELETE on students: validation/blocking happens before the change, body uses OLD (the row about to die) and RAISE(ABORT, ...) to refuse.
(2) AFTER INSERT on marks: react once the row safely exists, body uses NEW (the arriving values) to add NEW.score to the total.
Rule of thumb: BEFORE guards, AFTER reacts. And the NEW/OLD availability follows the event: INSERT brings NEW, DELETE leaves OLD, UPDATE carries both.
Quiz
An exam asks you to temporarily disable a trigger in SQLite. What is the correct answer?
- SQLite has no DISABLE/ENABLE TRIGGER: you DROP the trigger and re-CREATE it later
- ALTER TRIGGER log_marks_change DISABLE;
- DISABLE TRIGGER log_marks_change;
- Set trigger_enabled = 0 in the table's schema
Show the answer
SQLite has no DISABLE/ENABLE TRIGGER: you DROP the trigger and re-CREATE it later
Big engines (SQL Server, Oracle) offer DISABLE/ENABLE TRIGGER; SQLite does not, and it has no ALTER TRIGGER either. The honest SQLite workflow is DROP TRIGGER name; and CREATE it again when needed (keep the CREATE statement saved). This engine-difference is precisely why the syllabus lists 'disable and enable trigger': the expected answer is knowing SQLite's limitation, not inventing syntax.
Watch out
Three trigger traps
Inventing DISABLE syntax: in SQLite the answer is drop-and-recreate, full stop.
Wrong alias for the event: OLD inside an INSERT trigger, or NEW inside DELETE, is an error: match the alias to what the event actually has.
Logging with BEFORE: a BEFORE UPDATE log can record a change that then fails and never happens. Audit logs belong in AFTER triggers, once the change is real.
Theory
Triggers around you, and their cost
Bank statements (every transaction logged), inventory systems (stock decremented on each sale), your college's fee system flagging defaulters: trigger territory. One professional caution worth quoting: triggers run invisibly, so a table with many of them becomes hard to reason about. Use them for cross-cutting guarantees (audit, integrity), not for business logic a program should own. You can list a database's triggers via the sqlite_master table (type = 'trigger').
Summary
Key takeaways
- A trigger = stored SQL the database runs automatically on INSERT/UPDATE/DELETE of a table.
- Timing: BEFORE guards (validate, RAISE(ABORT)), AFTER reacts (log, cascade).
- NEW = incoming values, OLD = replaced values; INSERT has NEW only, DELETE OLD only, UPDATE both.
- CREATE TRIGGER name AFTER UPDATE ON table BEGIN ... END; DROP TRIGGER name; removes it.
- SQLite has NO DISABLE/ENABLE or ALTER TRIGGER: drop and re-create instead (exam favourite).
- Classic use: the marks audit log that no forgetful application can skip.
- Memory hook: CCTV over the register.