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

postgresql triggers status
Hao picture Hao · Apr 1, 2009 · Viewed 14.2k times · Source

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

Answer

tolgayilmaz picture tolgayilmaz · Apr 14, 2015

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.

Related questions

How do I temporarily disable triggers in PostgreSQL?

I'm bulk loading data and can re-calculate all trigger modifications much more cheaply after the fact than on a row-by-row basis. How can I temporarily disable all triggers in PostgreSQL?

Read More
Postgresql, update if row with some unique value exists, else insert

I have a URLs table. They contain (id int primary key, url character varying unique, content character varying, last analyzed date). I want to create trigger or something(rule may be), so each time i make insert from my java …

Read More
Get first date of month in postgres

I'm trying to get a 'date' type that corresponds to the first day of the current month. Basically one of my tables stores a date, but I want it to always be the first of the month, so I'm trying …

Read More