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?

score 4 · Answer 1 · answered Apr 23 '18 at 11:37

The effects of a trigger 'belong' to the statement that triggered it, so everything will be rolled back together (with or without explicit transactions):

> create table a(x int);
> create table b(y int check(y > 0));
> insert into b values(1);
> create function t() returns trigger as $$
  begin
    update b set y = -1;
    return null;
  end;
  $$ language plpgsql;
> create trigger at after insert on a execute procedure t();
> insert into a values(0);
ERROR:  new row for relation "b" violates check constraint "b_y_check"
DETAIL:  Failing row contains (-1).
CONTEXT:  SQL statement "update b set y = -1"
PL/pgSQL function t() line 1 at SQL statement
> select * from a;
 x
---
(0 rows)

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

1 Answers1