| Query goal: | Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented. |
| Notes about the query: | 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: | Do not let the system to do extra work. Ensure that trigger procedures are executed only if there is a real need of that. Specify WHEN clause with a Boolean expression that has to be satisfied to execute 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<>'INSTEAD OF' AND action_condition IS 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. |