Query goal: | Find tables where user-defined (non-system) triggers are used to implement referential integrity. In addition to table name show the triggers and the number of triggers. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the triggers and declare instead foreign key constraints. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
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, EXISTS (SELECT * FROM pg_constraint AS o WHERE t.tgconstraint=o.oid AND o.contype ='t') AS is_constraint_trigger, (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, 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 trigger_schema, trigger_table, string_agg(trigger_name || CASE WHEN is_constraint_trigger=TRUE THEN '(constraint trigger)' ELSE '' END || ' ' || action_timing || ' ' || event_manipulation || ' ' || 'FOR EACH ' || action_orientation || ' EXECUTE ' || routine_name, ',<br>' ORDER BY trigger_name) AS triggers, Count(*) AS number_of_triggers FROM Tr WHERE routine_name~ '^RI_FKey' GROUP BY trigger_schema, trigger_table ORDER BY trigger_schema, trigger_table; |
Collection name | Collection description |
---|---|
Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
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 . |
Category name | Category description |
---|---|
Relationships between tables | Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly. |
Triggers and rules | Queries of this category provide information about triggers and rules in a database. |
Reference |
---|
https://wiki.postgresql.org/wiki/Referential_Integrity_Tutorial_%26_Hacking_the_Referential_Integrity_tables |