Search code examples
pythonpostgresqldatabase-triggernotify

postgres trigger notify - what is better: before or after

I am using a postgres-DB and a pyton script, which should be notified for DB-Changes with the postgres NOTIFY-functionality. In all examples i can find for this topic the trigger in postgres is implemented with BEFORE like in this example:

CREATE TRIGGER notify_on_changes
  BEFORE UPDATE OR INSERT OR DELETE
  ON table_bla_bla
  FOR EACH ROW
  EXECUTE PROCEDURE notify_changes();

what is the reason for using BEFORE and not AFTER? I do not want to change anything before inserting/updating or deleting a row. Shouldn't it be better to use AFTER?

Solution

  • AFTER triggers have to be queued up in memory for later execution, so are less efficient.

    BEFORE triggers carry the risk that some other BEFORE trigger will modify the row after you have seen it but before it is written.