Goal These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Enable all the triggers of the table.
Data Source system catalog only
SQL Query 
SELECT 
n.nspname AS trigger_schema,
t.tgname AS trigger_name,
n.nspname AS table_schema,
c.relname AS table_name,
pc.conname AS constraint_name,
CASE WHEN pc.contype='f' THEN 'FOREIGN KEY'
WHEN pc.contype='p' THEN 'PRIMARY KEY' 
WHEN pc.contype='u' THEN 'UNIQUE'
WHEN pc.contype='t' THEN 'CONSTRAINT TRIGGER'
WHEN pc.contype='c' THEN 'CHECK'
ELSE 'EXCLUSION' END AS constraint_type
FROM pg_catalog.pg_namespace n INNER JOIN  pg_catalog.pg_class c
ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_trigger t
ON c.oid = t.tgrelid LEFT JOIN pg_catalog.pg_constraint pc
ON t.tgconstraint=pc.oid
WHERE t.tgenabled='D' AND t.tgisinternal=TRUE
ORDER BY n.nspname, c.relname, pc.conname;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('ALTER TABLE %1$I.%2$I ENABLE TRIGGER ALL;', n.nspname, c.relname) AS statements 
FROM pg_catalog.pg_namespace n INNER JOIN  pg_catalog.pg_class c
ON n.oid = c.relnamespace
WHERE (n.nspname, c.relname) IN 
(SELECT n.nspname, c.relname
FROM pg_catalog.pg_namespace n INNER JOIN  pg_catalog.pg_class c
ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_trigger t
ON c.oid = t.tgrelid LEFT JOIN pg_catalog.pg_constraint pc
ON t.tgconstraint=pc.oid
WHERE t.tgenabled='D' AND t.tgisinternal=TRUE)
ORDER BY n.nspname, c.relname;

Enable all the triggers of the table.

SQL Query to Generate FixDescription
SELECT format('ALTER TABLE %1$I.%2$I ENABLE TRIGGER ALL;', n.nspname, c.relname) AS statements 
FROM pg_catalog.pg_namespace n INNER JOIN  pg_catalog.pg_class c
ON n.oid = c.relnamespace
WHERE (n.nspname, c.relname) IN 
(SELECT n.nspname, c.relname
FROM pg_catalog.pg_namespace n INNER JOIN  pg_catalog.pg_class c
ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_trigger t
ON c.oid = t.tgrelid LEFT JOIN pg_catalog.pg_constraint pc
ON t.tgconstraint=pc.oid
WHERE t.tgenabled='D' AND t.tgisinternal=TRUE)
ORDER BY n.nspname, c.relname;
Enable all the triggers of the table.

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:

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.

Unused implementation elements

Queries of this catergory provide information about the database objects that are not used.

NameDescription
Relationships between tablesQueries 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 rulesQueries of this category provide information about triggers and rules in a database.
Unused implementation elementsQueries of this catergory provide information about the database objects that are not used.

Further reading and related materials:

Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Enabling NOCHECK on referential integrity constraints)
Reference
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Enabling NOCHECK on referential integrity constraints)