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 some indications about the state of enforcing constraints at the database level. The query does not count internal triggers.
Notes The query counts "ordinary" triggers as well as 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.
Type Sofware measure (Numeric values (software measures) about the database)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
SELECT Count( trigger_name) AS number_of_triggers
FROM (
SELECT DISTINCT trigger_schema ||'.'||event_object_table ||'.'|| trigger_name AS 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 evtname AS trigger_name
FROM pg_catalog.pg_event_trigger) AS foo;

Categories

This query is classified under the following categories:

NameDescription
Triggers and rulesQueries of this category provide information about triggers and rules in a database.