Module lessons (3/4)
Introduction to triggers
A trigger is a magic box (a sensor) that automatically runs a piece of logic BEFORE or AFTER an insert, update, or delete event in the data.\nBefore teaching a table to react, though, we need to give it a special "Trigger Function" written in PL/pgSQL that must end by returning a synthetic format called "TRIGGER".
These functions don't accept normal arguments in parentheses; instead they offer the magic built-in objects NEW and OLD for free, which contain the original or incoming row data from the table the sensor is watching!
CREATE OR REPLACE FUNCTION audit_deletions()
RETURNS TRIGGER AS $$
BEGIN
-- OLD.id is magically the id value of the poor record being deleted
INSERT INTO audit_log (table_name, record_id, action)
VALUES (TG_TABLE_NAME, OLD.id, 'DELETE');
RETURN OLD; -- Triggers before a Delete route must grant the exit
END;
$$ LANGUAGE plpgsql;This procedure does the hard work of writing to the log. Later, in a second step, it will be permanently wired to the events of the table we want to protect.
Try it yourself
We need to protect customers: no one under 18 years old (our virtual check) or, beyond fictional logic, we want to force the 'first_name' string passed at INSERT time to always be stored in uppercase in the database, ignoring any JS client formatting.\nWrite a Trigger Function called 'force_uppercase_name()'. It accepts no () arguments.\nIt returns TRIGGER. Create the body template.\nForce-assign the whole incoming object via the magic assignment 'NEW.first_name = UPPER(NEW.first_name);'. Then RETURN the reshaped NEW variable.
Show hint
CREATE OR REPLACE FUNCTION force_uppercase_name() RETURNS TRIGGER AS $$ BEGIN NEW.first_name = UPPER(NEW.first_name); RETURN NEW; END; $$ LANGUAGE plpgsql;
Solution available after 3 attempts
Create a Trigger Function 'protect_price_drops()' that blocks malicious updates where an attacker is about to save a ridiculously low price on a product.\nUse logic like 'IF NEW.unit_price < OLD.unit_price THEN', raising a native exception that brings everything crashing down.\nA fatal PL/pgSQL exception uses the form 'RAISE EXCEPTION ''Prezzo non abbassabile!'';'.
Show hint
BEGIN IF NEW.unit_price < OLD.unit_price THEN RAISE EXCEPTION 'Prezzo non abbassabile!'; END IF; RETURN NEW; END;
Solution available after 3 attempts