CODEWITHSUNDEEP
postgresqltriggerssqltransaction
sunprophit
sunprophit

Reputation: 1689

Queries and triggers execution order inside transaction

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;

Upvotes: 1

Views: 1798

Answers (1)

Houari
Houari

Reputation: 5621

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)

Upvotes: 3

Related Questions