Monday, 18 November 2013

Query to find the list of Triggers in SQL Server Database

Below query is used to find the list of triggers and its properties on current database.

               AS trigger_name
                        ,USER_NAME(SO.uid) AS trigger_owner
                        , AS table_schema
                        ,OBJECT_NAME(parent_obj) AS table_name
                        ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
                        ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
                        ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
                        ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
                        ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
                        ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
                        sysobjects SO
                        INNER JOIN sys.tables ST ON SO.parent_obj = ST.object_id
                        INNER JOIN sys.schemas SS ON ST.schema_id = SS.schema_id

                        SO.type = 'TR'