Goal Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level.
Notes The query counts table triggers (associated with a table and reacting to a data modification in it) and event triggers. It counts these over all the different schemas that contain user-defined schema objects. It discards triggers that the system creates automatically to enforce referential integrity. If one trigger is associated with multiple events (for instance, INSERT and UPDATE), then it counts it as one trigger. The query only returns data about schemas that have at least one table trigger.
Type Sofware measure (Numeric values (software measures) about the database)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH triggers AS (
SELECT trigger_schema, 'TABLE TRIGGER' AS trigger_type, event_object_table, trigger_name
FROM information_schema.triggers AS Tr
INNER JOIN information_schema.schemata S
ON Tr.trigger_schema=S.schema_name 
WHERE (Tr.trigger_schema = 'public'
OR S.schema_owner<>'postgres')
UNION ALL SELECT '' AS trigger_schema, 'EVENT TRIGGER' AS trigger_type, '' AS event_object_table, evtname AS trigger_name
FROM pg_catalog.pg_event_trigger)
SELECT trigger_schema, trigger_type, Count(DISTINCT event_object_table ||'.'|| trigger_name) AS number_of_triggers
FROM triggers
GROUP BY CUBE(trigger_schema, trigger_type)
ORDER BY trigger_schema, trigger_type;

Collections

This query belongs to the following collections:

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Find quick numeric overview of the database

Queries that return numeric values showing mostly the number of different types of database objects in the database

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Find quick numeric overview of the databaseQueries that return numeric values showing mostly the number of different types of database objects in the database

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.