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.

SELECT
                        SO.name AS trigger_name
                        ,USER_NAME(SO.uid) AS trigger_owner
                        ,SS.name 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]
FROM
                        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
WHERE

                        SO.type = 'TR'