Node TRIGGERS for PostgreSQL
[EN]
The node displays information about triggers in a schema in a PostgreSQL database
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 ns.nspname = $$SCHEMANAME
ORDER BY trg.tgname
Menu
Copy name - copy the trigger name 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 in use" state
Get DDL - generate the text of the trigger and the function it calls
Drop trigger - generate a command to delete a trigger
Get DDL
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
[RU]
Узел отображает информацию о триггерах в схеме в базе данных PostgreSQL
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 ns.nspname = $$SCHEMANAME
ORDER BY trg.tgname
Меню
Copy name - скопировать в буфер обмена имя триггера
Enable trigger - сформировать команду для перевода триггера в состояние "Активный"
Disable trigger - сформировать команду для перевода триггера в состояние "Не используется"
Get DDL - сформировать текст триггера и функции которую он вызывает
Drop trigger - сформировать команду на удаление триггера
Get DDL
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