Stored procedure for viewing trigger details

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