The list of all the queries

UPDATE triggers where WHEN clause has not been specified (the trigger could executed too often)

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;

Categories where the query belongs to

Category nameCategory description
PerformanceQueries of this category provide information about indexes in a database.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.

The list of all the queries