Query goal: | Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented. |
Notes about the query: | The query finds UPDATE triggers where the updated columns have not been specified. The query excludes triggers that according to the WHEN clause seem to react to a data change in any column. |
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 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 columns='{NULL}' AND action_condition!~'\*.*(IS[[:space:]]+DISTINCT[[:space:]]+FROM|<>|!=).*\*' ORDER BY trigger_schema, trigger_table, trigger_name; |
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 |
---|---|
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. |