Postgres with Drizzle · Chapter 22 of 40
The trigger mechanism
By the end of this chapter you can make Postgres run your code on every insert, update or delete: fill in a column, keep a count, log a change, or refuse the change altogether. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Database Powers
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Meera wants every profile to show when it was last edited and how many posts the member wrote. Compliance asked Vikram for a record of every profile change, before and after. And verified members must never be deleted by accident.
In chapter 10 you set updatedAt by hand in the update itself. That works only where someone remembers it. Dozens of endpoints update members, a script imports them, engineers fix things in psql: each one that forgets leaves a wrong timestamp, a wrong count or a hole in the audit record. A trigger lives in the database, so every change goes through it, whoever makes it.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
createUpdatedAtTrigger: updated_at that keeps itself current
Write createUpdatedAtTrigger(). It creates (or replaces) a trigger function named update_updated_at() that sets the new row's updated_at to the current time and returns the new row, and a trigger named trg_users_updated_at that runs it before every update, for each row of the users table.
Then it tests the trigger: insert a member (a username and an email are enough) and keep the updated_at it gets, wait about 50 milliseconds, change that member's bio, and read their updated_at again.
Send raw SQL with getPool().query. Return an object with triggerCreated (the value true) and updatedAtChanged (true when the second updated_at is later than the first). Afterwards: trg_users_updated_at exists on users.
- Task 2
createPostCountTrigger: count a member's posts
Write createPostCountTrigger(). It creates (or replaces) a trigger function named increment_post_count() that adds 1 to the post_count of the new post's author, and a trigger named trg_posts_increment that runs it after every insert, for each row of the posts table.
Then it tests it: insert a member with post_count 0, insert one post by that member (author_id and content are enough), and read the member's post_count.
Return an object with postCountAfterInsert: that post_count, a number. With a working trigger it is 1. Afterwards: trg_posts_increment exists on posts.
- Task 3
createAuditLogTrigger: a log of profile changes
Write createAuditLogTrigger(). It creates the table audit_log if it does not exist, with the columns id (serial primary key), table_name (text), operation (text), old_data (jsonb), new_data (jsonb) and changed_at (timestamptz, default now). It creates (or replaces) a trigger function named audit_trigger() that inserts one audit_log row with the table's name, the operation, the old row and the new row as JSON, and a trigger named trg_users_audit that runs it after every update, for each row of users.
Then it tests it: insert a member, change their bio, and count the rows of audit_log.
Return an object with auditEntries: that count, a number, not a string (1 here). Afterwards: audit_log and trg_users_audit exist.
- Task 4
createPreventDeleteTrigger: verified members cannot be deleted
Write createPreventDeleteTrigger(). It creates (or replaces) a trigger function named prevent_verified_delete() that raises an exception with the message Cannot delete verified users when the row being deleted is verified (is_verified is true), and otherwise lets the delete go ahead. A trigger named trg_prevent_verified_delete runs it before every delete, for each row of users.
Then it tests it: insert a verified member, try to delete them, and catch the error.
Return an object with errorCaught (true when the delete threw) and errorMessage (the error's message, which must contain Cannot delete verified users). Afterwards: the verified member is still there.
- Task 5
listTriggers: the triggers on a table
Write listTriggers(tableName). tableName is a string, a table's name. It returns the triggers on that table, from information_schema.triggers: one row per trigger and event.
Return result.rows: objects with trigger_name, event_manipulation and action_timing, ordered by trigger_name. The check creates trg_users_updated_at on users first and expects it in the list.
What you will learn
- A trigger function: PL/pgSQL code that returns TRIGGER and sees the row as NEW (after) and OLD (before).
- CREATE TRIGGER: run that function BEFORE or AFTER an insert, update or delete, FOR EACH ROW.
- RAISE EXCEPTION: refuse a change with your own message.
- information_schema.triggers: the list of triggers on a table.
More in Database Powers
Build The trigger mechanism yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.