Queries and triggers execution order inside transaction

Question

If trigger have been invoked inside of transactions is it guaranteed that all of the triggers (non-constraint/non-deferred triggers) will be finished before start of next query in transaction. For example I have following table:

Table a
id    name    is_updated
1     alice   False
2     alice   False
3     Alice   False

Row level before trigger defined on this table which sets is_updated to True if name field have been updated.

I execute following transaction:

BEGIN;
UPDATE table_a SET name = 'Alice' WHERE name = 'alice';
UPDATE table_a SET is_updated = False WHERE is_updated = True;
COMMIT;

Could it be so second UPDATE statement starts before all triggers have been finished? Or triggers run inside of transaction with statement which invoked triggers?

UPD1

Postgresql version is 9.3. And here is trigger and related function sources:

CREATE TRIGGER a_name_update_trigger
BEFORE UPDATE OF name
FOR EACH ROW
EXECUTE PROCEDURE on_a_name_update();

CREATE OR REPLACE FUNCTION on_a_name_update()
RETURNS TRIGGER AS $$
    BEGIN
        NEW.is_updated = True;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Can you please show your trigger defintion and your postgresql version? — Houari, Mar 10 '15 at 11:24

score 3 · Accepted Answer · answered Mar 11 '15 at 10:01

3

Nothing is wrong, except i think the definition of the trigger which should be something like:

CREATE OR REPLACE FUNCTION on_a_name_update()
RETURNS TRIGGER AS $$
BEGIN
  NEW.is_updated = true;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER a_name_update_trigger
BEFORE update of name ON table_a 
FOR EACH ROW
EXECUTE PROCEDURE on_a_name_update();

SQLFiddle

So, by executing your query:

BEGIN;
UPDATE table_a SET name = 'Alice' WHERE name = 'alice';
UPDATE table_a SET is_updated = False WHERE is_updated = True;
COMMIT;

The final update is as expected:

id    name    is_updated
1    "Alice"    False
2    "Alice"    False
3    "Alice"    False

In other words, the second update is executed after trigger is fired on rows(if col name is updated)

answered Mar 11 '15 at 10:01

Houari

5,326
3
31
54

Though is it possible that second UPDATE run before triggers end? I can't find it in docs. – sunprophit Mar 11 '15 at 10:49
No, you can't do that, because all triggers are executed in the same transaction. I think that if you explain what you really want to do, perhaps there is another way to achieve it. – Houari Mar 11 '15 at 11:07
I wanted to understand how triggers and others statements run inside of transactions so no unpredicted behavior would take place. Thanks. – sunprophit Mar 15 '15 at 21:44

Queries and triggers execution order inside transaction

1 Answers1