Technical Article

List of triggers in a database

,

Give the following information, about triggers in the database.

Parent_Name     
Parent_Type
Name                        
Type              
Insert
Update
Delete
IsFirstInsertTrigger
IsFirstUpdateTrigger
IsFirstDeleteTrigger
IsLastInsertTrigger
IsLastUpdateTrigger
IsLastDeleteTrigger

declare @dbname as sysname
declare @sql nvarchar(4000)
declare @tn as smallint
declare @trn as smallint

set @dbname = db_name()

set @sql = N'
select @t = max(datalength(rtrim(cast(object_name(o.parent_obj) as char)))), 
@tr = max(datalength(rtrim(cast(o.[name] as char))))
from ' + @dbname + '..sysobjects o
whereOBJECTPROPERTY(o.[id], ''IsTrigger'') = 1'

execute sp_executesql @sql, N'@t smallint output, @tr smallint output', @t=@tn output, @tr=@trn output

set @sql = N'
select cast(object_name(o.parent_obj) as char(' + str(@tn) + ')) as Parent_Name,
case 
when OBJECTPROPERTY(o.parent_obj, ''IsTable'') = 1 then ''Table''
when OBJECTPROPERTY(o.parent_obj, ''IsView'') = 1 then ''View''
else ''''
end as Parent_Type,
cast(o.[name] as char(' + str(@trn) + ')) as [Name],
case 
when (select cmptlevel 
from master.dbo.sysdatabases
      where [name] = db_name()) = 80 then 
case 
when OBJECTPROPERTY(o.[id], ''ExecIsInsteadOfTrigger'') = 1 then ''Instead Of Trigger''
else ''After Trigger''
end
 else ''After Trigger'' 
end as [Type],
case 
when OBJECTPROPERTY(o.[id], ''ExecIsInsertTrigger'') = 1 then ''X''
else '''' 
end as [Insert],
case 
when OBJECTPROPERTY(o.[id], ''ExecIsUpdateTrigger'') = 1 then ''X''
else '''' 
end as [Update],
case 
when OBJECTPROPERTY(o.[id], ''ExecIsDeleteTrigger'') = 1 then ''X''
else '''' 
end as [Delete],
case 
when OBJECTPROPERTY(o.[id], ''ExecIsFirstInsertTrigger'') = 1 then ''X''
else '''' 
end as [IsFirstInsertTrigger]
,
case 
when OBJECTPROPERTY(o.[id], ''ExecIsFirstUpdateTrigger'') = 1 then ''X''
else ''''
end as [IsFirstUpdateTrigger]
, 
case 
when OBJECTPROPERTY(o.[id], ''ExecIsFirstDeleteTrigger'') = 1 then ''X''
else ''''
end as [IsFirstDeleteTrigger],
case 
when OBJECTPROPERTY(o.[id], ''ExecIsLastInsertTrigger'') = 1 then ''X''
else '''' 
end as [IsLastInsertTrigger]
,
case 
when OBJECTPROPERTY(o.[id], ''ExecIsLastUpdateTrigger'') = 1 then ''X''
else ''''
end as [IsLastUpdateTrigger]
, 
case 
when OBJECTPROPERTY(o.[id], ''ExecIsLastDeleteTrigger'') = 1 then ''X''
else ''''
end as [IsLastDeleteTrigger]
from ' + @dbname + '..sysobjects o
WHEREOBJECTPROPERTY(o.[id], ''IsTrigger'') = 1
order by object_name(o.parent_obj), o.[name]'

execute sp_executesql @sql

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating