Goal This query identifies BEFORE triggers whose primary purpose is to enforce data integrity and business rules (validating data and raising exceptions without performing any modifying DML operations), but which conditionally bypass execution based on the trigger nesting level (pg_trigger_depth()). Database invariants and business rules must be enforced unconditionally, regardless of whether a data modification was initiated directly by an external client query or indirectly via another trigger, stored procedure, or cascading operation. Because pure validation triggers perform only read operations and do not execute INSERT, UPDATE, DELETE, or MERGE statements, it is mathematically impossible for them to cause infinite recursion. Therefore, incorporating pg_trigger_depth() into validation logic is a severe architectural flaw that creates a critical data integrity loophole: whenever secondary triggers modify the table, all essential validity checks are silently skipped.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Low Many 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 (
      COALESCE(action_condition, '') ~* '\ypg_trigger_depth\s*\('
      OR routine_definition ~* '\ypg_trigger_depth\s*\('
  )  
  AND routine_definition ~* '\yRAISE\s+EXCEPTION\y'  
  AND routine_definition !~* '(\yINSERT\s+INTO\y|\yDELETE\s+FROM\y|\yMERGE\s+INTO\y|\yUPDATE\s+[^;]+?\ySET\y)'
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

Remove the pg_trigger_depth() check from the trigger's WHEN clause and routine body. Business rules and data validation logic must execute unconditionally across all nesting levels. Because pure validation triggers do not execute modifying DML statements (INSERT, UPDATE, DELETE, MERGE), infinite recursion is technically impossible. Removing the pg_trigger_depth() condition closes the data integrity loophole and ensures that invalid data cannot be saved during nested trigger or cascade operations.

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.