Query below lists tables with their triggers in a database with details.
SELECT SubjectTableDatabaseName as TableDatabase,
TableName as TableName,
when 'A' then 'AFTER'
when 'B' then 'BEFORE'
when 'I' then 'INSTEAD OF'
WHEN 'U' THEN 'UPDATE'
WHEN 'I' THEN 'INSERT'
WHEN 'D' THEN 'DELETE'
WHEN 'Y' THEN 'ENABLED'
WHEN 'N' THEN 'DISABLED'
END as Status,
WHEN 'R' THEN 'ROW'
WHEN 'S' THEN 'STATEMENT'
end as TriggerKind,
RequestText as Definition,
CreateTimeStamp as CreateDate
ORDER BY TableDatabase,
- TableDatabase - name of the trigger table database
- TableName - name of the trigger table
- TriggerName - name of the trigger
- ActionTime - trigger activation time: before, after
- Event - specific SQL operation: insert, update or delete
- Status - trigger status
- TriggerKind - trigger fires for each:
- ROW - row modified in the subject table
- STATEMENT - statement that modifies the subject table
- Definition - SQL definiton of trigger
- CreateDate - trigger creation time
- One row represents one trigger
- Scope of rows: all triggers in a database
- Ordered by table database name and table name