Query below lists triggers in a database with their details.

Query

SELECT  TriggerName,
        SubjectTableDatabaseName as TableDatabase,
        TableName as TableName,
        CASE ActionTime
            when 'A' then 'AFTER'
            when 'B' then 'BEFORE'
            end,
        CASE Event
            WHEN 'U' THEN 'UPDATE'
            WHEN 'I' THEN 'INSERT'
            WHEN 'D' THEN 'DELETE'
            END,
        CASE EnabledFlag
            WHEN 'Y' THEN 'ENABLED'
            WHEN 'N' THEN 'DISABLED'
            END as Status,
        CASE Kind
            WHEN 'R' THEN 'ROW'
            WHEN 'S' THEN 'STATEMENT'
            end as TriggerKind,
        RequestText as Definition,
        CreateTimeStamp as CreateDate
FROM DBC.TriggersV
ORDER BY TriggerName;

Columns

• TriggerName - name of the trigger
• TableDatabase - name of the trigger table database
• TableName - name of the trigger table
• ActionTime - trigger activation time: before, after
• Event - specific SQL operation: insert, update or delete
• Status - trigger status
  • ENABLED
  • DISABLED
• 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

Rows

• One row represents one trigger
• Scope of rows: all found triggers in a database
• Ordered by trigger name

Sample results