INF: A Stored Procedure to Display Trigger Information

• Microsoft SQL Server versions 6.0, 6.5

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.

   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
   */ 

   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 <trigger name> to find out more '
   select @q1 = @q1 + 'about how a trigger is defined'

   select 'Remarks:' = @q1
   return 0
   go 

Keywords : kbcode kbprg SSrvProg SSrvStProc
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbhowto