2

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?

1 Answer 1

4

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.