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

Question

My googling-fu is failing me. How to know if a PostgreSQL trigger is disabled or not?

are the triggers enabled by default while creating em? Is explicit ALTER TABLE ENABLE TRIGGER necessary? — krithikaGopalakrishnan, Sep 07 '17 at 06:19

score 23 · Answer 1 · edited Feb 08 '18 at 16:52

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.

score 17 · Accepted Answer · edited Jan 26 '15 at 03:10

17

It's 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.

edited Jan 26 '15 at 03:10

nhahtdh

55,989
15
126
162

answered Apr 01 '09 at 07:32

Rafael

18,349
5
58
67

score 7 · Answer 3 · answered Jan 20 '16 at 12:19

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).

score 0 · Answer 4 · answered Dec 15 '20 at 16:18

Starting from @tolgayilmaz reply I created a simply function to be used around my project

CREATE OR REPLACE FUNCTION trigger_exists_on_table(schema_name text, table_name text, trigger_name text)
RETURNS boolean AS
$body$
declare
    _sql text;
    _boolean_exists boolean;
begin
    _sql := format('
        SELECT EXISTS (
            SELECT 1
            FROM pg_trigger
            WHERE tgrelid = ''%s.%s''::regclass AND tgname = ''%s'' AND tgenabled != ''D''
        )
    ', schema_name, table_name, trigger_name);

    execute _sql into _boolean_exists;

    return _boolean_exists;
end
$body$
LANGUAGE plpgsql;

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

4 Answers4