| 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 |
|
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.
| Name | Description |
|---|---|
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |