List table triggers in Azure SQL database

2019-02-11

Table of Contents:


    Article for: Azure SQL Database 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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept