SQL Server – Trigger – Metadata

Background

Need to document our triggers.

SQL

Code


; with cteTriggerEvent
(
      [object_id]
    , [type]
)
as
(

    select
            [object_id]
                = tblST.object_id

        , [type]
            =
                stuff
                (
                    (

                        select 
                                ','
                                + tblSTE_Inner.[type_desc]

                        from sys.trigger_events tblSTE_Inner

                        where  tblST.[object_id]
                                    = tblSTE_Inner.[object_id]

                        for xml path('')
                    )
                    , 1
                    , 1
                    , ''

                )


    from sys.triggers tblST

)

select 

        [object] = 
                        object_schema_name(tblST.parent_id)
                    + '.'
                    + object_name(tblST.parent_id)
    , [trigger] 
        = tblST.[name]
        
    , [disabled]
        
        = case tblST.[is_disabled]
                when 1 then 'Yes'
                else 'No'
            end

    , [isInsteadofTrigger]
        = case tblST.is_instead_of_trigger
                when 1 then 'Yes'
                else 'No'
            end
                
    , tblSTE.[type]

from   sys.triggers tblST

inner join cteTriggerEvent tblSTE

        on tblST.[object_id] = tblSTE.[object_id]

order by
          [disabled]
        , [object]
        , [trigger]

Output

Output – msdb

 

Source Control

GitHub

DanielAdeniji/SQLServerTriggerMetadata
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s