SELECT
n.nspname AS trigger_schema,
t.tgname AS trigger_name,
n.nspname AS table_schema,
c.relname AS table_name,
'TRIGGER' AS type
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c,
pg_catalog.pg_trigger t
WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid AND t.tgenabled='D' AND t.tgisinternal=FALSE
UNION ALL SELECT NULL AS trigger_schema,
evtname AS trigger_name,
NULL AS table_schema,
NULL AS table_name,
'EVENT TRIGGER' AS type
FROM pg_catalog.pg_event_trigger
WHERE evtenabled='D'
ORDER BY table_Schema, table_name, trigger_name;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
SELECT format('ALTER TABLE %1$I.%2$I ENABLE TRIGGER %3$I;', n.nspname, c.relname, t.tgname) AS statements
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c,
pg_catalog.pg_trigger t
WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid AND t.tgenabled='D' AND t.tgisinternal=FALSE
ORDER BY n.nspname, c.relname, t.tgname;
Enable the trigger.
SELECT format('DROP TRIGGER %1$I ON %2$I.%3$I RESTRICT;', t.tgname, n.nspname, c.relname) AS statements
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c,
pg_catalog.pg_trigger t
WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid AND t.tgenabled='D' AND t.tgisinternal=FALSE
ORDER BY n.nspname, c.relname, t.tgname;
Drop the trigger.
SELECT format('ALTER EVENT TRIGGER %1$I ENABLE;', evtname) AS statements
FROM pg_catalog.pg_event_trigger
WHERE evtenabled='D'
ORDER BY evtname;
Enable the event trigger.
SELECT format('DROP EVENT TRIGGER %1$I RESTRICT;', evtname) AS statements
FROM pg_catalog.pg_event_trigger
WHERE evtenabled='D'
ORDER BY evtname;
Drop the event trigger.
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
Unused implementation elements
Queries of this catergory provide information about the database objects that are not used.