List table triggers in Azure SQL database

  • 11th February, 2019
Article for: SQL Server Oracle database PostgreSQL

Query below lists tables with their triggers.

Query

select schema_name(tab.schema_id) + '.' + tab.name as [table],
    trig.name as trigger_name,
    case when is_instead_of_trigger = 1 then 'Instead of'
        else 'After' end as [activation],
    (case when objectproperty(trig.object_id, 'ExecIsUpdateTrigger') = 1 
            then 'Update ' else '' end
    + case when objectproperty(trig.object_id, 'ExecIsDeleteTrigger') = 1 
            then 'Delete ' else '' end
    + case when objectproperty(trig.object_id, 'ExecIsInsertTrigger') = 1 
            then 'Insert ' else '' end
    ) as [event],
    case when trig.[type] = 'TA' then 'Assembly (CLR) trigger'
        when trig.[type] = 'TR' then 'SQL trigger' 
        else '' end as [type],
    case when is_disabled = 1 then 'Disabled'
        else 'Active' end as [status],
    object_definition(trig.object_id) as [definition]
from sys.triggers trig
    inner join sys.objects tab
        on trig.parent_id = tab.object_id
order by schema_name(tab.schema_id) + '.' + tab.name, trig.name;

Columns

  • table - name of the trigger table (for table triggers) with schema name
  • trigger_name - name of the trigger
  • activation - trigger activation time: After or Instead of
  • event - specific SQL operation: Insert, Update or Delete
  • type - object type:
    • Assembly (CLR) trigger
    • SQL trigger
  • status - trigger status
    • Active
    • Disabled
  • definition - SQL definiton of trigger

Rows

  • One row represents one trigger
  • Scope of rows: all column, table triggers in a database
  • Ordered by schema and table name

Sample results

0
There are no comments. Click here to write the first comment.