How can you tell if a trigger is enabled in PostgreSQL?

How can you tell if a trigger is enabled in PostgreSQL?

The SQL below will do the work. It displays all triggers in your current database.

SELECT pg_namespace.nspname, pg_class.relname, pg_trigger.*
FROM pg_trigger
JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace  

If tgenabled is D, the trigger is disabled. All other values (documented here) indicate, that it is enabled in some way.

BTW. If you want to check the triggers for a specific table, the query is a bit shorter:

SELECT * FROM pg_trigger
WHERE tgrelid = your_schema.your_table::regclass

The cast to the regclass type gets you from qualified table name to OID (object id) the easy way.

Its my first day with postresql, but I think you can check the trigger state via pg_trigger system table: http://www.postgresql.org/docs/current/static/catalog-pg-trigger.html

The columns you will need are tgrelid and tgenabled.

How can you tell if a trigger is enabled in PostgreSQL?

SELECT EXISTS (
    SELECT  tgenabled
    FROM    pg_trigger
    WHERE   tgname=your_unique_trigger_name AND
            tgenabled != D
);

If you know the trigger name is unique the above will return true (t) if the your_unique_trigger_name trigger is enabled:

 exists
--------
 t
(1 row)

If disabled it would return false (f).

Leave a Reply

Your email address will not be published.