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

Rate

Share

Share

Rate