CODEWITHSUNDEEP
sqldatabasepostgresqltriggerspostgresql-triggers
Saravanan
Saravanan

Reputation: 1440

How to show the trigger(s) associated with a view or a table in PostgreSQL?

I have one requirement that I have to get the list of triggers associated to the given table/view.
Can anyone help me to find the triggers for a table in PostgreSQL?

Upvotes: 88

Views: 111971

Answers (7)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can show all user-defined triggers in the current database with information_schema.triggers and pg_trigger as shown below. *There is no way to show all triggers in all databases at once:

SELECT * FROM information_schema.triggers;

SELECT * FROM pg_trigger;

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21915

This will return all the details you want to know

select * from information_schema.triggers;

or if you want to sort the results of a specific table then you can try

SELECT event_object_table
      ,trigger_name
      ,event_manipulation
      ,action_statement
      ,action_timing
FROM  information_schema.triggers
WHERE event_object_table = 'tableName' -- Your table name comes here
ORDER BY event_object_table
     ,event_manipulation;

the following will return table name that has trigger

select relname as table_with_trigger
from pg_class
where pg_class.oid in (
        select tgrelid
        from pg_trigger
        );

Upvotes: 119

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

The problem with the view information_schema.triggers (besides being slow) is, per documentation:

The view triggers contains all triggers defined in the current database on tables and views that the current user owns or has some privilege other than SELECT on.

Meaning, you only get to see triggers you have appropriate privileges on.

To see all triggers for a table, look in the system catalog pg_trigger

SELECT tgname
     , tgisinternal, pg_get_triggerdef(oid) AS definition  -- optional additions
FROM   pg_trigger
WHERE  tgrelid = 'myschema.mytbl'::regclass;  -- optionally schema-qualified

I added tgisinternal to show which are internal triggers, and pg_get_triggerdef(oid) to get the definition (mostly relevant for non-internal triggers).

Works for tables and views.
Or you could use a GUI like pgAdmin that displays the list under the table node in the object browser.

Upvotes: 45

Raj RD
Raj RD

Reputation: 107

\df will list all the functions including triggers.

\dft will list all the triggers.

Upvotes: 3

jeremyg
jeremyg

Reputation: 1

I noticed that infoschema does NOT contain key relation table information about triggers (at least in postgres 10). pg_triggers does contain this info. Also noticed that datagrip will not script relation tables when you script the triggers, so i'm assuming that it uses infoschema to script them (and then your table would be missing the relation tables, and the trigger functions referencing them would fail). PG documentation says that columns in infoschema for action_reference_old_table applies to a feature not available in postgres(10), but i'm definitely using them, and they definitely show up in pg_triggers. FYI.

Upvotes: 0

Chanakya
Chanakya

Reputation: 31

select    tgname
    ,relname
    ,tgenabled
    ,nspname    from    pg_trigger 
    join    pg_class    on    (pg_class.oid=pg_trigger.tgrelid) 
    join    pg_namespace    on    (nspowner=relowner);


tgenabled (To check if its disabled)

O = trigger fires in "origin" and "local" modes, 
D = trigger is disabled, 
R = trigger fires in "replica" mode,
A = trigger fires always.

Upvotes: 2

Touko
Touko

Reputation: 11779

On psql command-line tool you can also use \dS <table_name> (from https://serverfault.com/questions/331024/how-can-i-show-the-content-of-a-trigger-with-psql)

Upvotes: 11

Related Questions