What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was always executed before update.

So I made the following example:

I made a function that updates a table when the status is typing but with a delay of 10 seconds.

  RETURNS trigger AS
    if new.status = 'Typing' then
        update image set status = 'ToTyping', path = 'Real path' from pg_sleep(5) where id = old.id;
    end if;
    return null;
  COST 100;
ALTER FUNCTION fai_prueba()
  OWNER TO postgres;

then I have the following trigger

create trigger tai 
after update on image 
for each row execute procedure fai_prueba();

but when I run an UPDATE the query does not end until the delay ends

UPDATE image 
    SET  path='fake path'
       , status= 'Typing' 
WHERE id=5;

>Query returned successfully: 0 rows affected, 10042 ms execution time.

So is it possible that the update query ends before the trigger?

  • No, the query cannot "end" and the trigger be delayed. The actions of both have to be committed (or rejected) together. Oct 27, 2016 at 19:50
  • 2
    quote from the manual: "The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted); or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed);"
    – user1822
    Oct 27, 2016 at 20:07
  • 2
    If you want to change values of the row that is being processed, you should use a before trigger and assign the values direcly, e.g. new.status := 'ToTyping'; instead of using an (expensive) UPDATE statement in an after trigger.
    – user1822
    Oct 27, 2016 at 20:08

1 Answer 1


There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless.

In a BEFORE trigger, the trigger is executed BEFORE the DML statement is executed.. So you have a chance to modify the :NEW (old :OLD in case of DELETE) row BEFORE it is inserted/updated/deleted... But anything you want to do you can do (like check data in a separate table and issue an update/insert/whatever in that table, etc). If, for some reason, the trigger causes an EXCEPTION, then execution ceases and the DML statement is never executed.

In an AFTER trigger, the trigger is executed AFTER the DML statement is executed. You have already lost any ability to modify the :NEW and :OLD records and have it actually mean anything. However, you can still do whatever you want in the trigger. If the trigger causes an exception, for PostgreSQL, this will cause a rollback of the original DML. I do not believe this is true for all RDBMSs - but am having a hard time finding an example right now.

A common use of a BEFORE trigger is to set a timestamp column to "now" before the data has been inserted.

A common use of an AFTER trigger is to populate an audit/history table with the changes.

In either case, you would usually want to issue a ROLLBACK if you find an exception has been raised.

  • 1
    How about performance? Would UPDATE queries (or locks for SELECT) be faster for AFTER triggers?
    – Marius
    Nov 9, 2022 at 12:59

Your Answer

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

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