Goal This query identifies row-level BEFORE INSERT and BEFORE UPDATE triggers whose primary purpose is not to raise an exception, but which never reference the NEW record outside of the RETURN clause. In PostgreSQL, BEFORE triggers are specifically intended either to validate incoming data (raising exceptions upon failure) or to manipulate column values by modifying NEW. If a trigger does neither, it typically indicates that its logic (such as logging or updating related tables) should be relocated to an AFTER trigger, or that the trigger contains obsolete, redundant code.
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	
    FROM pg_namespace n
    JOIN pg_class c ON n.oid = c.relnamespace
    JOIN pg_trigger t ON c.oid = t.tgrelid
    JOIN pg_proc p ON t.tgfoid = p.oid
    JOIN pg_namespace np ON p.pronamespace = np.oid
    CROSS JOIN (VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
    WHERE (t.tgtype::integer & em.num) <> 0 
      AND NOT t.tgisinternal 
      AND NOT pg_is_other_temp_schema(n.oid)
)
SELECT 
    trigger_schema, 
    trigger_table, 
    action_timing, 
    event_manipulation, 
    action_orientation, 
    action_condition, 
    routine_name, 
    regexp_replace(routine_definition, '[\r\n]', '<br>', 'g') AS routine_definition
FROM Tr
WHERE action_timing = 'BEFORE'
  AND event_manipulation IN ('INSERT', 'UPDATE')
  AND action_orientation = 'ROW'  
  AND routine_definition !~* '\yRAISE\s+EXCEPTION\y'
  AND routine_definition !~* '(\yNEW\.|\yNEW[[:space:]]*:=)'  
  AND (action_condition IS NULL OR action_condition !~* '\yNEW\.')
ORDER BY 
    trigger_schema, 
    trigger_table, 
    routine_name;

Fixing Suggestion

Refactor the trigger timing from BEFORE to AFTER if it performs side-effect operations (like auditing), or drop the trigger entirely if it no longer serves an active purpose.

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:

Triggers and rules

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

NameDescription
Triggers and rulesQueries of this category provide information about triggers and rules in a database.