Search code examples
postgresqltriggers

Proper way to delete triggers in PostgreSQL

Short question...

Must one use the DROP TRIGGER operation: https://www.postgresql.org/docs/9.1/static/sql-droptrigger.html

Or would simply deleting entries from the pg_trigger table suffice?

Solution

  • The proper way is the only one. Use drop trigger, even if the deleting from pg_trigger seems to work fine. In fact it does not. After you manually delete an entry from pg_trigger you can get the error (not immediately but when you least expect it) like this:

    ERROR: could not find tuple for trigger 123456

    This is because of Postgres stores information about triggers on a table also in pg_depend. As an exemplary result, you will not be able to drop the table.

    Update. Some explanations concerning dependencies between a trigger and its associated function.

    It is not a trigger function that depends on a trigger, but vice versa, a trigger depends on a function. So if you want to drop a trigger and a trigger function at once you should drop the function with the option cascade, e.g.:

    drop function a_trigger_function() cascade;

    Without the option, you cannot drop a trigger function when a trigger exists (before a trigger was dropped). So statements in another answer to the question are misleading.

    Note also that there are situations in which removing a function simultaneously with a trigger is not appropriate because a function may be used in many triggers.