Module lessons (4/4)
Practical triggers and audit
Now that we've learned how to forge smart bullets (the Trigger Functions), we need to mount them on real Primed Weapons waiting for an event: here come the actual Triggers! A trigger formally attaches a previously created function and instructs it to fire "BEFORE" or "AFTER" a particular CRUD event on a given table.
There are many options; for example FOR EACH ROW fires it once per affected row, so if a bulk query is a GLOBAL UPDATE over 5 million rows, it would fire 5 million micro-executions — be careful with that!
CREATE TRIGGER sync_inventory_trigger
-- Event options
AFTER INSERT OR UPDATE ON order_items
-- Modus operandi (singular or global macro-block FOR EACH STATEMENT)
FOR EACH ROW
-- Run the forged action
EXECUTE FUNCTION update_stock();The trigger is invisible to JavaScript and will autonomously keep the stock perfectly consistent based strictly on updates submitted safely to the engine.
Automating Updated_At
There is an evergreen business problem where we forget to pass { updated_at: new Date() } in our JS update models. A trigger function set_updated_at combined with a BEFORE UPDATE trigger removes this nuisance, silently updating every record change autopoietically.
Try it yourself
Create a trigger named 'trg_force_uppercase'.\nAttach it in the 'BEFORE INSERT' phase on the 'customers' table.\nForce row-by-row computation 'FOR EACH ROW' and invoke the execution of the 'force_uppercase_name()' function.\nNote: also recreate the UDF 'force_uppercase_name' (since the environment is isolated) at the top of the code, then append the trigger declaration.
Show hint
Text syntax: create the function first, then add CREATE TRIGGER trg_force_uppercase BEFORE INSERT ON customers FOR EACH ROW EXECUTE FUNCTION force_uppercase_name();
Solution available after 3 attempts
Kill the sensor! Sometimes we want to push raw seed files or temporary native changes through, bypassing noisy audits.\nDropping a trigger is incredibly straightforward (you don't delete the organic function, you only delete the sensor binding).\nUse the 'DROP TRIGGER' keyword, specifying the name and the global attachment.
Show hint
Remember to specify where from. DROP TRIGGER trg_force_uppercase ON customers;
Solution available after 3 attempts