A procedure has to be called. And all it takes is one application, one intern with psql or one import process writing straight into the table to bypass the whole thing. A trigger is the opposite: code that runs on its own, without anybody invoking it, at the exact moment a row is inserted, modified or deleted. It's the only piece in this course capable of guaranteeing that something always happens, no matter who does the writing.

This is where 09-02's promise gets kept: the business rules a CHECK can't express because they need to query another table. You'll see the full anatomy, the four axes that define a trigger's behaviour, the NEW and OLD variables with the mechanism that makes them useful —returning NULL cancels the operation—, the four canonical cases applied to GreenStore with complete code, and the INSTEAD OF triggers that make 10-01's views writable. And their dangers, given as much weight as the advantages, because a trigger is invisible logic: it makes an UPDATE do things you didn't write.

1. Anatomy: trigger function + CREATE TRIGGER
2. The four axes
3. NEW, OLD, TG_OP and the return value
4. Case 1: auditing price changes
5. Case 2: maintaining a denormalized total
6. Case 3: the rule a CHECK can't express
7. Case 4: automatic stock deduction, and whether it's worth it
8. INSTEAD OF on views
9. Firing order and management
10. The dangers
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

In PostgreSQL a trigger is two objects, and that separation is disconcerting at first:

1. A function returning the special TRIGGER type that takes no declared parameters.
2. A CREATE TRIGGER saying on which table, when and at what granularity that function runs.

The advantage of splitting them is that one function can serve several triggers on several tables —hence TG_TABLE_NAME— and that's exactly what makes a generic audit function useful.

(Every trigger, function and table in this lesson is an example: none of them is part of the canonical GreenStore schema from 01-06. The orders.total column in case 2, in particular, is not canonical.)

The combination decides almost everything:

BEFORE versus AFTER in one sentence: in BEFORE the row doesn't exist yet, so you can change it or cancel it, but its auto-generated id isn't available yet in an INSERT; in AFTER it already exists and its id is real, but returning anything other than NULL changes nothing. Rule: validate and modify in BEFORE; react in AFTER.

And the WHEN isn't just elegance: WHEN (OLD.price IS DISTINCT FROM NEW.price) avoids running the function on the millions of UPDATEs that don't touch the price. It's the cheapest optimization a trigger has.

Inside a FOR EACH ROW trigger function there are two row-typed variables, and you don't always have both:

Using NEW in a DELETE or OLD in an INSERT gives record "new" is not assigned yet. Hence a function serving several operations has to ask with TG_OP ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'). There are more variables available —TG_TABLE_NAME, TG_WHEN, TG_LEVEL, TG_ARGV[] with the CREATE TRIGGER's arguments— but those two solve nearly everything.

And now the mechanism that makes triggers useful: what the function's return value means.

Read that slowly, because these are the two capabilities no other tool in this course has: in a BEFORE ... FOR EACH ROW, returning NULL cancels the operation —the row isn't inserted, the UPDATE isn't applied, and the INSERT 0 0 is the only clue— and returning NEW with changed fields stores those changes. In a DELETE, what you return is OLD. In an AFTER, the return value is ignored and by convention you write RETURN NULL.

Cancelling silently is dangerous. If the row is being rejected because of a business rule, a RAISE EXCEPTION with a clear message is almost always better than RETURN NULL: whoever wrote the INSERT deserves to know why nothing happened. Save the NULL for deliberate, documented discards, such as filtering junk rows out of a bulk load.

The most incontestable use of a trigger: recording what changed, who changed it and when. No application can forget to do it, because it doesn't depend on any application.

A single row: the second UPDATE fired nothing thanks to the WHEN. Three decisions worth copying from this example: AFTER, because you only audit what actually happened; IS DISTINCT FROM instead of <>, because with NULL involved <> isn't true and a change from NULL to a value would be lost (04-03); and TIMESTAMPTZ with now(), not whatever time the client sends.

01-06 explained why orders does not have a total column: it's an aggregate you'd have to keep in sync. When computing it on the fly and 10-01's view are no longer enough —an order listing that sorts and filters by total over millions of rows— the way out is to store it and maintain it with a trigger.

(First 6 of 20 orders; SELECT SUM(total) FROM orders gives 727.95.) And if you now delete a line from order 1, its total recomputes on its own.

Three details that make it work and that always get forgotten: COALESCE(NEW.order_id, OLD.order_id), because in a DELETE there's no NEW; the outer COALESCE(..., 0), because when you delete the last line the SUM returns NULL and the column is NOT NULL; and the initial backfill, because a newly created trigger knows nothing about the past — that's the number-one mistake when denormalizing.

And the honest warning: this is what you do when there's no other way. You've traded a cheap read for an extra write on every INSERT, UPDATE and DELETE of lines, you've created a path for the data to drift out of sync and you've moved a rule somewhere invisible. Before you get here, try a view (10-01), an index (module 8) and a materialized view.

This is 09-02's promise. The rule is "a review can only be written by a customer who has bought that product", and a CHECK can't express it: a CHECK only sees the columns of its own row, and this requires querying orders and order_lines.

And the 12 existing reviews are still valid: they all belong to customers who bought that product, so the trigger breaks nothing. Compare it with exercise 3b in 01-06, where this exact INSERT worked because no constraint prevented it. Not any more.

Two essential warnings. The first: a trigger validates what happens from now on, not what's already there. When adding a rule to a table with data in it, check first how many rows break it. The second is subtler: this check isn't immune to concurrency. Between the EXISTS's SELECT and the review's INSERT, another transaction could cancel the order; under READ COMMITTED the trigger wouldn't see it (09-04). For most business rules that's acceptable; for an invariant that must hold always, you need explicit locks or SERIALIZABLE.

Technically it's the simplest of the four:

It works, and products' CHECK (stock >= 0) stops it going negative: if somebody tries to sell more than there is, the trigger's UPDATE fails and the whole transaction is rolled back, including the line that caused it.

But is it worth it? There's no single answer here, and it's worth setting it against 10-04's sp_confirm_order:

The criterion: if stock is a sacred invariant and there are several write paths, trigger. If there's a single controlled entry point and the message to the user matters, procedure. And if you pick the trigger, add a BEFORE that validates with a decent message instead of letting the CHECK raise the error.

10-01 left open what to do with views that are not automatically updatable —the ones with JOINs, aggregates or computed columns. The answer is an INSTEAD OF trigger, which replaces the operation with whatever you decide.

Now INSERT INTO v_sales_detail (order_id, product_id, quantity) VALUES (1, 3, 2); works: the view joins four tables, but the trigger knows that only order_lines needs writing to and where to get the price from. Three rules: INSTEAD OF triggers only exist on views, they're always FOR EACH ROW, and you need three separate triggers —INSERT, UPDATE, DELETE— if you want the view to be fully writable. There's also the rule system (CREATE RULE), older and discouraged today: it rewrites the query before running it, with surprising effects when volatile functions are involved.

When several triggers compete for the same moment on the same table, PostgreSQL runs them in alphabetical order by name.

Having the order depend on the name is as fragile as it sounds: renaming trg_validate to trg_zvalidate can change the system's behaviour without anyone touching a line of logic. If two triggers depend on the order between them, merge them into one; if they don't, so much the better.

DISABLE TRIGGER is essential for a bulk load: importing a million lines with the stock trigger enabled means a million extra UPDATEs. You disable it, load, recompute the aggregates in one go and re-enable it. And it comes with a trap: while it's disabled, ordinary writes bypass it too, so do it in a controlled window and don't forget to switch it back on.

Name your triggers with a prefix and a pattern: trg_<table>_<what it does>. In a database with two hundred objects, finding out why an UPDATE does something odd starts with being able to list that table's triggers and understand their names.

They deserve as much space as the advantages, because a badly placed trigger is one of the hardest things there is to debug.

• Invisible logic. "The UPDATE did something I didn't write" is the sentence that defines the problem. The application's SQL doesn't mention the trigger anywhere; whoever is debugging has to suspect it exists. It's why many teams limit them to auditing and integrity, which are predictable uses.
• Cost per row. FOR EACH ROW means one execution per row. An UPDATE touching 500,000 rows runs the function 500,000 times, with its queries inside. What takes 0.2 ms on one row takes 100 seconds on half a million.
• Cascades and recursion. A trigger on order_lines that updates orders can fire a trigger on orders that updates order_lines... and so on until stack depth limit exceeded. A trigger that modifies its own table is directly recursive; you cut it off with a WHEN that detects there's nothing to do, or with pg_trigger_depth() = 0.
• Hard to test. They can't be invoked in isolation: you have to provoke the operation that fires them, and the tests end up being integration tests.
• Transactional, for better and for worse. A trigger runs inside the transaction that fires it: if it fails, everything is rolled back, including the original operation. That's what you want for integrity; it's a disaster if inside it you call an external service or send an email (10-04, exercise 3).
• Interaction with COPY and with FKs. COPY fires row triggers, so an import can be far slower than expected. And PostgreSQL's foreign keys are implemented as internal triggers, which don't show up in \d but do in pg_trigger with tgisinternal = true.

Dialect note: the concept is universal, the syntax isn't. PostgreSQL separates function and trigger, and it's the only one on this list that does. MySQL 8 puts the body inside the CREATE TRIGGER, only supports FOR EACH ROW and has no INSTEAD OF and no statement triggers. SQL Server works per statement with the INSERTED and DELETED pseudo-tables instead of NEW/OLD, and does have INSTEAD OF. Oracle uses :NEW and :OLD with colons and adds compound triggers. SQLite has them, with FOR EACH ROW mandatory and no TRUNCATE.

• Forgetting the RETURN in a BEFORE ... FOR EACH ROW. Falling off the end of the function returns NULL, and that cancels the operation silently: rows that don't get inserted with no error at all.
• Using NEW in a DELETE or OLD in an INSERT. record "new" is not assigned yet. Ask TG_OP or use COALESCE(NEW.x, OLD.x).
• Creating a validation trigger and not reviewing the existing data. It only applies to the future; the rows that already break the rule stay.
• Denormalizing without the initial backfill. The trigger maintains the column from now on, but the previous 20 rows stay at 0.
• Comparing with <> instead of IS DISTINCT FROM in the WHEN. With NULL involved, <> isn't true and the change doesn't get audited.
• Putting a FOR EACH ROW where a FOR EACH STATEMENT would do, or putting an expensive query inside a row trigger. It multiplies by the number of rows.
• Making external calls from a trigger. It runs inside the transaction: if that's rolled back, the email has already gone out and can't be unsent.
• Depending on alphabetical order between two triggers. It's fragile. If the order matters, merge them.
• Leaving DISABLE TRIGGER ALL switched on. The bulk load finishes and nobody re-enables it; from then on the rules don't apply and nobody finds out until months later.
• Tip: prefix trg_ and pattern trg_<table>_<action>. And document it alongside the CREATE TABLE, with COMMENT ON TRIGGER, saying what it does and why.
• Tip: RAISE EXCEPTION with a clear message, not RETURN NULL. Silence is the worst possible error message.
• Tip: faced with inexplicable behaviour, \d table is the first command. Triggers are listed right there, and very often the explanation is in that line.

Design a complete audit trail for products: a product_audit table recording any INSERT, UPDATE or DELETE with the operation, the old row and the new one in text form, the user and the moment. (1) BEFORE or AFTER? ROW or STATEMENT? (2) Write the function and the trigger using TG_OP. (3) How would you make it generic so it also serves customers and orders without duplicating code?

On section 6's review trigger, reason out your answers and then check them: (1) What happens if you try to insert Hugo's review (customer 14) on product 1? And if customer 1 reviews product 1? (2) Why is the trigger BEFORE and not AFTER? Would it work the same as an AFTER? (3) Order 6 is cancelled: if its customer tried to review a product they only bought in that order, would it pass validation? Find the line in the query responsible.

A colleague has put this trigger on products and now no UPDATE ever finishes:

(1) What error does it give and why? (2) Rewrite it properly, with no inner UPDATE. (3) In what other case, besides this one, can a trigger end up in a loop?

Solution 1

1. AFTER ... FOR EACH ROW. AFTER because you only audit what really happened —in BEFORE, the operation could still fail on a CHECK or an FK and you'd be left with a record of something that never occurred— and FOR EACH ROW because you audit each row, not each statement. 2:

3. It already is. The function doesn't mention products anywhere: it uses TG_TABLE_NAME to know where it came from and NEW::TEXT / OLD::TEXT to dump the whole row whatever its structure. All you need is another identical CREATE TRIGGER on customers and orders. The two CASEs are necessary because OLD doesn't exist in an INSERT nor NEW in a DELETE. In a real system you'd store to_jsonb(NEW) in a JSONB column instead of text, and then the audit trail would be queryable key by key — which is 10-06.

Solution 2

1. Hugo's fails with ERROR: Customer 14 has not bought product 1: he has no orders at all. Customer 1's on product 1 passes, because the olive oil is on line 1 of their order 1. In fact that review already exists: it's number 1 of the twelve.

2. It's BEFORE because it validates, and validating means rejecting before writing. As an AFTER it would also work —the RAISE EXCEPTION would abort the transaction and the inserted row would be rolled back— but pointless work would have been done: writing the row, updating its indexes and undoing it all. BEFORE to validate, AFTER to react, and here BEFORE also leaves the door open to correcting NEW instead of rejecting it.

3. It wouldn't pass, because of the line AND o.status <> 'cancelled'. It's a deliberate business decision: a cancelled order isn't a purchase, so it doesn't earn the right to review. And it's exactly the kind of nuance that only fits in a trigger: a CHECK can't query orders.status, and neither can a foreign key.

Solution 3

1. It gives ERROR: stack depth limit exceeded. The trigger fires AFTER UPDATE on products and does an UPDATE on products, which fires it again, which updates again… until the stack runs out. It's section 10's direct recursion, and there's no automatic protection against it.

2. The right way is not to update anything: modifying the row being written is done in a BEFORE, by changing NEW and returning it.

This is the canonical pattern for the updated_at column that almost every production table carries, and the reason it's implemented with BEFORE and not with AFTER.

3. With cascades across tables: A writes to B, a trigger on B writes to A, and the cycle closes without either one modifying itself. Also with ON DELETE CASCADE combined with delete triggers, and with two triggers that keep reactivating each other on the same table. The defences: a WHEN that cuts things off once there's nothing left to change (WHEN (OLD.total IS DISTINCT FROM NEW.total)), the check IF pg_trigger_depth() > 1 THEN RETURN NULL; END IF;, or redesigning so the cycle doesn't exist.

Code that runs on its own, with everything that implies:

• A trigger is two objects: a function returning TRIGGER and a CREATE TRIGGER saying on which table, when and at what granularity it runs. The separation lets a generic function serve several tables, with TG_TABLE_NAME and TG_OP.
• The four axes: when (BEFORE validates and modifies, AFTER reacts, INSTEAD OF replaces), what (INSERT/UPDATE/DELETE/TRUNCATE), granularity (FOR EACH ROW versus FOR EACH STATEMENT) and WHEN (condition), which avoids firing for nothing.
• NEW and OLD aren't always both there, and the return value is the key mechanism: in a BEFORE ... FOR EACH ROW, RETURN NULL cancels the operation and a modified RETURN NEW alters it. In AFTER it's ignored.
• The four GreenStore cases: auditing price changes with WHEN (OLD.price IS DISTINCT FROM NEW.price) —the most incontestable use—; a denormalized orders.total that adds up to €727.95 and demands an initial backfill; the review rule a CHECK can't express because it queries another table, closing 09-02; and stock deduction, with the honest comparison against 10-04's sp_confirm_order.
• INSTEAD OF triggers make writable the views 10-01 couldn't update automatically: views only, always FOR EACH ROW, and one per operation.
• The firing order is alphabetical by name, which is fragile: if two triggers depend on the order, merge them. You list them with \d and with pg_trigger, switch them off with ALTER TABLE ... DISABLE TRIGGER for bulk loads and name them trg_<table>_<action>.
• And the dangers, given equal weight: invisible logic that catches out whoever is debugging, cost per row in bulk loads, cascades and recursion up to stack depth limit exceeded, difficulty testing, and the fact that they run inside the transaction that fires them. A trigger is the right answer when the rule must hold no matter who writes; it's a patch when it replaces a CHECK or hides business logic that ought to be visible.

With views, CTEs, window functions, procedures and triggers, the relational SQL toolbox is nearly complete. One case is missing that the relational model handles badly by design: data whose shape isn't fixed. An olive oil has acidity and variety; a cream, ingredients and skin type; next year somebody will want to store each product's carbon footprint. Adding a column per attribute is unworkable, and building a key-value pair table is the classic, painful remedy. In the module's last lesson, JSON and semi-structured data, you'll see PostgreSQL's JSONB type: how to build documents, how to reach into them with ->, ->> and JSONPath, how to index them with GIN —08-03's outstanding promise—, how to turn them back into rows so you can keep using all the SQL in this course, and —most importantly— what should never go in a JSON.