Query goal: | Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. |
Notes about the query: | The query finds triggers that are associated with a table as well as event triggers, which react to schema modifications. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Enable the disabled trigger or drop it. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
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 query | 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. |
Collection name | Collection 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 . |
Category name | Category 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. |
Reference |
---|
https://en.wikipedia.org/wiki/Dead_code |