Топ-100
 
CerebroSQL

Table triggers in PostgreSQL

Node displays information about table triggers

PostgreSQL table trigger list.jpg
Menu
  • Copy name - copy the name of the trigger to the clipboard

  • Enable trigger - generate a command to switch the trigger to the "Active" state

  • Disable trigger - generate a command to switch the trigger to the "Not used" state

  • Get DDL - form the text of the trigger and the function it calls

  • Drop trigger - generate a command to remove a trigger

List of triggers
Pane

select trg.tgname,
        CASE trg.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        end as trigger_type,
       case trg.tgtype::integer & cast(28 as int2)
         when 16 then 'UPDATE'
         when 8  then 'DELETE'
         when 4  then 'INSERT'
         when 20 then 'INSERT, UPDATE'
         when 28 then 'INSERT, UPDATE, DELETE'
         when 24 then 'UPDATE, DELETE'
         when 12 then 'INSERT, DELETE'
       end as trigger_event,
       ns.nspname||'.'||tbl.relname as trigger_table,
       ns.nspname,
       obj_description(trg.oid) as remarks,
         case
          when trg.tgenabled='O' then 'ENABLED'
            else 'DISABLED'
        end as status,
        case trg.tgtype::integer & 1
          when 1 then 'ROW'::text
          else 'STATEMENT'::text
        end as trigger_level,
        trg.oid 
FROM pg_trigger trg
JOIN pg_class tbl on trg.tgrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
  WHERE  trg.tgrelid = $$TABLEOID

Get DDL
Panel

select p.prosrc,
       p.pronamespace,
       p.oid,
       pg_get_functiondef(p.oid) "def"
  from pg_catalog.pg_trigger t, pg_catalog.pg_proc p
where p.oid=t.tgfoid 
   and t.oid = $$TRIGGEROID  
   
   
SELECT pg_catalog.pg_get_triggerdef(t.oid, true) "ddl"
  FROM pg_catalog.pg_trigger t
WHERE t.oid = $$TRIGGEROID