Microsoft KB Archive/167135

= INFO: A Stored Procedure to Display Trigger Information =

PSS ID Number: 167135

Article Last Modified on 2/22/2005

-

The information in this article applies to:


 * Microsoft SQL Server 6.0
 * Microsoft SQL Server 6.5
 * Microsoft SQL Server 2000 (all editions)

-



This article was previously published under Q167135



SUMMARY
The stored procedure outlined in the MORE INFORMATION section of this article will display information for triggers defined within a database. The report includes trigger name, trigger type, associated database, and the table on which the trigger is defined. This information is not available from sp_depends.



MORE INFORMATION
The following stored procedure accepts at most one parameter, which may be any one of the following:


 * Database name: The report displays a row for each trigger in the specified database.
 * Table name: The report lists all triggers defined on the specified table within the current database.
 * Trigger name: The output shows information for the specified trigger.
 * Null: As Database name, above, but for the current database only.

Compile the following procedure, (sp_helptrigger), in the master database in order to make it available server-wide.

if exists (select * from sysobjects where id = object_id('sp_helptrigger') and sysstat & 0xf = 4) drop procedure sp_helptrigger GO

create procedure sp_helptrigger @object_name varchar(30) = null as /* This stored procedure can be used to display information on triggers including ** what table the trigger is defined on. **   ** Parameter: ** @object_name. **   ** If @object_name is a database all triggers will be displayed for that database. ** If @object_name is a table all triggers created on that table will be displayed. ** If @object_name is a trigger a single triggers will be reported. ** If @object_name is null all triggers in the current database will be reported. **   ** Version History: ** Date     Description Of Changed          Changed By    ** 14-04-97   Original Version                Gerard Conroy */ set nocount on declare @object_id int, @db_len tinyint, @tab_len tinyint, @trg_len tinyint, @q1 varchar(255), @q2 varchar(255), @db_name varchar(30)

create table #temp1 (tr_name varchar(30) null, tr_id int, table_id int, table_name varchar(30) null, tr_type char(1))

/*   ** If object name is null display everything for current database */ if @object_name is null begin select @db_name = db_name goto display_output end

select @object_name = rtrim(ltrim(@object_name))

/*   ** If object name is a database display everything for specified database */

if exists (select * from master..sysdatabases where name = @object_name) begin select @db_name = @object_name goto display_output end

select @db_name = db_name

/*   ** If object name is a table display everything for the specified table */ if exists (select * from sysobjects where name = @object_name and type in ('U','S')) begin create table #temp2 (tr_name varchar(30) null, tr_id int, table_id int, table_name varchar(30) null, tr_type char(1))

insert into #temp2 select name, id, deltrig, null, 'X'     from sysobjects where type = 'TR' and id = (select deltrig from sysobjects where name=@object_name and     deltrig <> 0) insert into #temp2 select name, id, deltrig, null, 'X'     from sysobjects where type = 'TR' and id = (select updtrig from sysobjects where name=@object_name and     updtrig <> 0)

insert into #temp2 select name, id, deltrig, null, 'X'     from sysobjects where type = 'TR' and id = (select instrig from sysobjects where name=@object_name and     instrig <> 0)

insert into #temp1 select distinct tr_name, tr_id, table_id, table_name, tr_type from #temp2

goto display_with_temp end

/*     ** If object name is a trigger null display everything for specified trigger */      if exists (select * from sysobjects where name = @object_name and type =      'TR') begin

insert into #temp1 select name, id, deltrig, null, 'X'     from sysobjects where type = 'TR' and name = @object_name

goto display_with_temp end

/*     ** Invalid object name */      select 'Warning:' = 'Parameter not recognised. Acceptable parmeters are database name, table name, trigger name or null.' return 4

/*     ** Format and display output. */

display_output:

select @q1 = 'insert into #temp1 select name, id, deltrig, null, X from ' select @q2 = @db_name + '..sysobjects where type = TR'

exec(@q1 + @q2)

/*     ** Execute from this point if #temp1 was created earlier. */

display_with_temp:

select @q1 = 'update #temp1 set table_name = name, tr_type = (case when     deltrig = tr_id then D when instrig = tr_id then I when updtrig =      tr_id then U end ) from #temp1, ' select @q2 = @db_name + '..sysobjects where table_id = id and type in     (U,S)'

exec(@q1 + @q2)

select @db_len = datalength(@db_name) + 2 if (@db_len < 10) select @db_len = 10

select @tab_len = (select max(datalength(table_name)) from #temp1) + 2 if (@tab_len < 7 or @tab_len is null) select @tab_len = 7

select @trg_len = (select max(datalength(tr_name)) from #temp1) + 2 if (@trg_len < 9 or @trg_len is null) select @trg_len = 9

select @q1 = 'select database = convert(varchar(' + convert(char(2),@db_len) select @q1 = @q1 + '),  + @db_name + ), table = convert(varchar(' + convert(char(2),@tab_len) select @q2 = '), table_name), trigger = convert(varchar(' + convert(char(2),@trg_len) select @q2 = @q2 + '), tr_name), trigger type = case tr_type when D then delete when I then insert when U then update else unknown end from #temp1'

exec (@q1 + @q2)

select @q1 = 'Enter sp_helptext to find out more ' select @q1 = @q1 + 'about how a trigger is defined'

select 'Remarks:' = @q1 set nocount off return 0 go

