Goal This query identifies row-level BEFORE UPDATE triggers that update a row's modification timestamp column (e.g., NEW.viimase_muutm_aeg := CURRENT_TIMESTAMP(0);), but lack a WHEN (OLD.* IS DISTINCT FROM NEW.*) condition to verify that the row data actually changed. Without this condition, executing a no-op UPDATE statement (where the new column values are identical to the existing values) still fires the trigger and overwrites the modification timestamp. This corrupts audit accuracy by recording a false modification time and forces PostgreSQL to perform unnecessary physical tuple writes, WAL logging, and index maintenance for a row whose business data did not change.
Notes Queries the PostgreSQL system catalogs (pg_trigger, pg_proc, pg_class, pg_namespace) and information_schema views to inspect row-level BEFORE UPDATE triggers. The query looks for trigger routines that assign a value to a modification-time attribute on the NEW record (matching muutm, update, change, or modif), where neither the trigger's WHEN clause (action_condition) nor the routine body compares OLD.* and NEW.* using IS DISTINCT FROM, <>, or !=. In the returned routine definition, newline characters are replaced with HTML line break (<br>) tags for better readability in a web browser.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH Tr AS (
    SELECT
        n.nspname AS trigger_schema,
        t.tgname AS trigger_name,
        em.text AS event_manipulation,        
        c.relname AS trigger_table, 
        np.nspname AS routine_schema,
        p.proname AS routine_name,
        p.prosrc AS routine_definition,
        (SELECT DISTINCT action_condition 
         FROM INFORMATION_SCHEMA.triggers AS it 
         WHERE it.trigger_schema = n.nspname 
           AND it.event_object_table = c.relname 
           AND it.trigger_name = t.tgname) AS action_condition,    
        CASE t.tgtype::integer & 1
            WHEN 1 THEN 'ROW'
            ELSE 'STATEMENT'
        END AS action_orientation,
        CASE t.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        END AS action_timing,
        EXISTS (
            SELECT 1 
            FROM pg_constraint AS c 
            WHERE c.oid = t.tgconstraint 
              AND c.contype = 't'
        ) AS is_constraint_trigger,
        t.tginitdeferred AS is_init_deferred
    FROM pg_namespace n,
        pg_class c,
        pg_trigger t,
        pg_proc p,
        pg_namespace np,
        (VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
    WHERE n.oid = c.relnamespace 
      AND c.oid = t.tgrelid 
      AND t.tgfoid = p.oid
      AND p.pronamespace = np.oid
      AND (t.tgtype::integer & em.num) <> 0 
      AND NOT t.tgisinternal 
      AND NOT pg_is_other_temp_schema(n.oid)
)
SELECT 
    Tr.trigger_schema, 
    trigger_table, 
    action_timing, 
    event_manipulation, 
    action_orientation, 
    action_condition, 
    string_agg(event_object_column::text, ',<br>' ORDER BY event_object_column) AS update_columns, 
    is_constraint_trigger,  
    is_init_deferred, 
    Tr.trigger_name, 
    routine_name, 
    regexp_replace(routine_definition, '[\r\n]', '<br>', 'g') AS routine_definition
FROM Tr 
LEFT JOIN information_schema.triggered_update_columns AS TUC 
    ON Tr.trigger_schema = TUC.trigger_schema 
   AND Tr.trigger_name = TUC.trigger_name 
   AND Tr.trigger_table = TUC.event_object_table
WHERE action_timing = 'BEFORE'
  AND event_manipulation = 'UPDATE'
  AND action_orientation = 'ROW'
  AND routine_definition ~* '\yNEW\.[[:alnum:]_]*(muutm|update|change|modif)[[:alnum:]_]*[[:space:]]*:?='
  AND COALESCE(action_condition, '') !~* '\*.*(IS[[:space:]]+DISTINCT[[:space:]]+FROM|<>|!=).*\*'  
  AND routine_definition !~* '\y(OLD|NEW)(\.\*)?[[:space:]]+(IS[[:space:]]+DISTINCT[[:space:]]+FROM|<>|!=)[[:space:]]+(OLD|NEW)(\.\*)?'
GROUP BY 
    Tr.trigger_schema, 
    Tr.trigger_table, 
    action_timing, 
    event_manipulation, 
    action_orientation, 
    action_condition, 
    is_constraint_trigger,  
    is_init_deferred, 
    Tr.trigger_name, 
    routine_name, 
    regexp_replace(routine_definition, '[\r\n]', '<br>', 'g')
ORDER BY 
    Tr.trigger_schema, 
    Tr.trigger_table, 
    event_manipulation, 
    action_timing, 
    action_orientation, 
    Tr.trigger_name;

Fixing Suggestion

Add a WHEN (OLD.* IS DISTINCT FROM NEW.*) clause to the CREATE TRIGGER definition so that the trigger function is invoked and the modification timestamp is updated only when at least one column value in the row has genuinely changed.

Collections

This query belongs to the following collections:

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 .

NameDescription
Find problems automaticallyQueries, 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 .

Categories

This query is classified under the following categories:

Result quality depends on names

Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.

Triggers and rules

Queries of this category provide information about triggers and rules in a database.

NameDescription
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.