Stored procedure for viewing trigger details
This is a stored procedure for viewing details of trigger. It accepts trigger name as a optional parameter. When specified, it shows details of only that trigger. If no trigger name is specified shows the details of all the triggers in that database. this SP is created in master database but can be called from any database. This stored procedure makes use of OBJECTPROPERTY, which provides usefull information about objects in database.
Use Master
if(exists(select * from sysobjects where name='sp_helptrigger2'))
drop procedure sp_helptrigger2
Go
Create procedure sp_helptrigger2 @trigger_name sysname = NULL
As
select object_name(id) as 'Trigger Name', object_name(parent_obj) as 'Table name',
Case objectproperty(id,'ExecIsDeleteTrigger') When 1 Then 'Yes' ELSE 'No' End as 'Delete Trigger' ,
Case objectproperty(id,'ExecIsDeleteTrigger') When 1 Then Case objectproperty(id,'ExecIsFirstDeleteTrigger')
When 1 Then 'First'
Else Case objectproperty(id,'ExecIsLastDeleteTrigger')
When 1 Then 'Last'
Else 'Not Specified' End
End
Else 'N/A'
End as 'Delete Fire Order',
Case objectproperty(id,'ExecIsUpdateTrigger') When 1 Then 'Yes' ELSE 'No' End as 'Update Trigger' ,
Case objectproperty(id,'ExecIsUpdateTrigger') When 1 Then Case objectproperty(id,'ExecIsFirstUpdateTrigger')
When 1 Then 'First'
Else Case objectproperty(id,'ExecIsLastUpdateTrigger')
When 1 Then 'Last'
Else 'Not Specified' End
End
Else 'N/A'
End as 'Update Fire Order',
Case objectproperty(id,'ExecIsInsertTrigger') When 1 Then 'Yes' ELSE 'No' End as 'Insert Trigger' ,
Case objectproperty(id,'ExecIsInsertTrigger') When 1 Then Case objectproperty(id,'ExecIsFirstInsertTrigger')
When 1 Then 'First'
Else Case objectproperty(id,'ExecIsLastInsertTrigger')
When 1 Then 'Last'
Else 'Not Specified' End
End
Else 'N/A'
End as 'Insert Fire Order',
Case objectproperty(id,'ExecIsTriggerDisabled') When 1 Then 'Yes' Else 'No' End as 'Disabled'
from sysobjects where objectproperty(id,'IsTrigger')=1 and ISNULL(name,'')=ISNULL(ISNULL(@trigger_name,name),'')
Go
