How to check if trigger exists in PostgreSQL?

Question

I want to verify correctness of database migrations which add triggers to some tables. I'm using sqitch, so I'd like to find a way to check it with SQL queries. I believe it should be possible with postgres system tables, but I currently can't find a way to do this.

klin · Accepted Answer · 2019-12-30T09:46:41.077

Use the catalog pg_trigger.

A simple lookup for a table books:

select tgname
from pg_trigger
where not tgisinternal
and tgrelid = 'books'::regclass;

    tgname     
---------------
 books_trigger
(1 row)

Using pg_proc to get the source of the trigger function:

select tgname, proname, prosrc 
from pg_trigger
join pg_proc p on p.oid = tgfoid
where not tgisinternal
and tgrelid = 'books'::regclass;

    tgname     |    proname    |                    prosrc
---------------+---------------+------------------------------------------------
 books_trigger | books_trigger |                                               +
               |               | begin                                         +
               |               |     if tg_op = 'UPDATE' then                  +
               |               |         if new.listorder > old.listorder then +
               |               |             update books                      +
               |               |             set listorder = listorder- 1      +
               |               |             where listorder <= new.listorder  +
               |               |             and listorder > old.listorder     +
               |               |             and id <> new.id;                 +
               |               |         else                                  +
               |               |             update books                      +
               |               |             set listorder = listorder+ 1      +
               |               |             where listorder >= new.listorder  +
               |               |             and listorder < old.listorder     +
               |               |             and id <> new.id;                 +
               |               |             end if;                           +
               |               |     else                                      +
               |               |         update books                          +
               |               |         set listorder = listorder+ 1          +
               |               |         where listorder >= new.listorder      +
               |               |         and id <> new.id;                     +
               |               |     end if;                                   +
               |               |     return new;                               +
               |               | end
(1 row)

Example of the pg_get_triggerdef() function usage:

select pg_get_triggerdef(t.oid) as "trigger declaration"
from pg_trigger t
where not tgisinternal
and tgrelid = 'books'::regclass;

                                             trigger declaration                
--------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER books_trigger BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE books_trigger()
(1 row)

In a Sqitch verify script you can use an anonymous code block, e.g.:

do $$
begin
    perform tgname
    from pg_trigger
    where not tgisinternal
    and tgrelid = 'books'::regclass;
    if not found then 
        raise exception 'trigger not found';
    end if;
end $$;

I've actually tried this and was quite confused. Weird thing is: i'm getting 7 `tgname`s looking like this `RI_ConstraintTrigger_c_195564`, instead of 1. I'm using postgresql 9.4. — dredozubov, Oct 16 '15 at 18:36
They are internal constraint triggers. Use `not tgisinternal` to skip them, like in the edited answer. — klin, Oct 16 '15 at 18:58
This solution might not work for sqitch as asked in the question. I having the same problem. Sqitch needs your query your to return an error in order to correctly verify the deploy code. All the queries mentioned in the above solution will only give empty result if the trigger doesn't exists. Since no error error is thrown, sqitch will assume it is verified correctly. — pratpor, Dec 30 '19 at 08:41
From what I understood on how sqitch works, it first runs the `deploy` script which will be creating adding the new trigger. It then runs the `verify` script which checks the if the trigger is added correctly. If verify script runs without any errors, it will assume everything is fine even if `deploy` failed for some reason. If the `verify` script would have thrown some error, sqitch would have got to know that something failed so it will run the `revert` script. — pratpor, Dec 30 '19 at 09:21
@pratpor - Use `DO` command with `ASSERT` or `RAISE` In the `verify` script. See the updated answer. — klin, Dec 30 '19 at 09:46

score 3 · Answer 2 · answered Nov 13 '21 at 19:40

to check by trigger name you can do it:

select trigger_name from information_schema.triggers 
WHERE trigger_name = 'your_trigger_name'

Also can select with other information.

select event_object_schema as table_schema,
       event_object_table as table_name,
       trigger_schema,
       trigger_name,
       string_agg(event_manipulation, ',') as event,
       action_timing as activation,
       action_condition as condition,
       action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
         table_name;

Column details

table_schema - name of the table schema
table_name - name of the trigger table
trigger_schema - name of the trigger schema
trigger_name - name of the trigger
event - specific SQL operation: Insert, Update or Delete
activation - trigger activation time: After, Instead of or BEFORE
condition - trigger activation condition
definition - definition of trigger - in postgreSQL it is always EXECUTE PROCEDURE function_name()

If you don't want to select event_manipulation, you may remove the group by in the query.

reference

How to check if trigger exists in PostgreSQL?

2 Answers2

Linked