| Query goal: | Do not let the system to do extra work. Ensure that trigger procedures are executed only if there is a real need of that. Find UPDATE triggers that could be executed too often because unneeded executions are not prevented. |
| Notes about the query: | The query finds UPDATE triggers where the WHEN clause is missing or the list of updated columns is not specified. The query takes into account that one cannot specify WHEN clause in case of INSTEAD OF triggers. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | Low (Many false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Add WHEN clause or specify a list of columns where updating data will cause execution of the trigger. |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT trigger_schema, trigger_table, trigger_name, event_manipulation, action_orientation, action_timing, action_condition, columns FROM (SELECT T. trigger_schema, T.event_object_table AS trigger_table, T.trigger_name, T.action_condition, T.event_manipulation, T.action_orientation, T.action_timing, array_agg(event_object_column::text) AS columns FROM (SELECT * FROM information_schema.triggers WHERE event_manipulation='UPDATE') AS T LEFT JOIN information_schema.triggered_update_columns AS TUC ON T.trigger_schema=TUC.trigger_schema AND T.trigger_name=TUC.trigger_name AND T.event_object_table=TUC.event_object_table GROUP BY T. trigger_schema, T.event_object_table, T.trigger_name, T.event_manipulation, T.action_orientation, T.action_timing, T.action_condition) AS f WHERE (action_timing<>'ISTEAD OF' AND action_condition IS NULL) OR columns='{NULL}' ORDER BY trigger_schema, trigger_table, trigger_name; |
| Category name | Category description |
|---|---|
| Performance | Queries of this category provide information about indexes in a database. |
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |