Does encapsulating a statement in a transaction include the triggers it fires?

Question

I have an INSERT that will fire a TRIGGER which will UPDATE an attribute on another TABLE. I need to make sure that if the INSERT is successful and the UPDATE inside the TRIGGER isn't then the whole thing should be rolled back.

Does encapsulating the INSERT in a transaction guarantee this?

If not, how can I do this?

Answer 1

The trigger is part of the database transaction that contains the original INSERT.

Any error inside the transaction (that is not handled) will cause the whole transaction to be rolled back, just like nothing ever happened. That will include the INSERT that caused the trigger to run.

So there is nothing you have to do; it will work the way you want out of the box.

You can explicitly handle an error inside a transaction by setting SAVEPOINTs and using ROLLBACK TO SAVEPOINT (or by using a BEGIN ... EXCEPTION ... END block in PL/pgSQL, which does the same under the hood).

To trigger an exception, either run an SQL statement that causes an error (like SELECT 1/0) or use the RAISE EXCEPTION statement in PL/pgSQL (there are similar ways for other procedural languages).