INFO: A Stored Procedure to Display Trigger Information

MORE INFORMATION

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

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