1

I've created a function which inserts in an audit table some data about the row that just has been deleted, and created an AFTER DELETE per-row trigger on the table I want to audit.

This is working fine, I'm seeing my audit table getting a new row inserted each time I delete a row from the table I want to audit.

I'd like to which whether there are known circumstances under which PostgreSQL would not run an AFTER DELETE trigger. I've read https://www.postgresql.org/docs/current/trigger-definition.html and could not find any, but I'd like to be sure.

3
  • I've updated my question to change "would not run this AFTER DELETE trigger" to "would not run an AFTER DELETE trigger". To clarify, my question is to know if there are specific known gotchas related to AFTER DELETE triggers, or triggers in general, which would lead to a trigger to not be fired.
    – Florent2
    Sep 28, 2021 at 15:29
  • A trigger can be enabled or disabled at any time, otherwise your trigger should be able to evaluate somehow if it content must be executed or not.
    – McNets
    Sep 28, 2021 at 15:37
  • My trigger is meant to always execute, there is no conditional within its function to avoid execution. My question is whether there are cases in PostgreSQL where the deletion of a row would not fire a trigger enabled on the table.
    – Florent2
    Sep 28, 2021 at 15:42

1 Answer 1

1

If the row is actually deleted, the AFTER trigger is fired - as long as it's not disabled.

If the DELETE fails for any reason, an exception is raised. Then the trigger is not fired, you get an error message, and the row is not actually deleted. (Or the trigger is fired and the trigger function raises an exception, or another, later trigger raises an exception, same result: error, rollback, row not deleted.)

What can happen is that another BEFORE trigger cancels the DELETE silently - by ending the trigger function with RETURN NULL; instead of RETURN OLD;. Then the trigger is not fired, you don't get an error message, and the row is not actually deleted.

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.