• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Show me the triggers.

I have a database with tons of triggers. I'd like to view them by the table they act on. Maybe I'm dense, but you can't seem to do that. And the Ergonomic but useless SQL Server Enterprize Manager doesn't seem to let you see triggers at all.  What I want is:

Table 1:
  Trigger_1
  Trigger_1A

Table_2:
  Triger_2

(As an aside, does SQL7 support multiple insert triggers on the same table?)
0
dmag
Asked:
dmag
1 Solution
 
Gustavo Perez BuenrostroCommented:
I don't know if exist a system SP that gives you all the information you need. However, you can use next one I wrote:

create proc spShowTriggers
@snTableName sysname=null
as
begin
  set nocount on
  declare @liId int
  declare CR_Tables cursor local
  for select id
        from sysobjects S
       where S.xtype="U"
         and (S.id=object_id(@snTableName) or
              @snTableName is null)
         and (select count(1)
                from sysobjects
               where parent_obj=S.id
                 and xtype='TR')>0
           
  open CR_Tables
  while (1=1)
    begin
      fetch next from CR_Tables into @liId
      if @@fetch_status<>0 break
      print object_name(@liId)
      select cast(name as varchar) as 'Trigger Name:'
        from sysobjects
       where parent_obj=@liId
         and xtype='TR'
    end
end

Syntax:
spShowTriggers [@snTableName =] 'table name'

Arguments:
[@snTableName =] 'table name'
Is the name of the table you want to retrieve all triggers.

Result Sets:
If spShowTriggers is executed with no arguments, trigger information of all tables that exist in the current database is returned.

Example (on pubs database):

spShowTriggers

dept
Trigger Name:                  
------------------------------
emp_update

employee
Trigger Name:                  
------------------------------
employee_insupd

StaffTimes
Trigger Name:                  
------------------------------
trgUpdateAgg
0
 
bbychkovCommented:
look at sp_helptrigger system stored procedure. You have to pass table name as parameter. Try this script:

declare tableList cursor static for select name from sysobjects where type='U'
declare @tableName varchar(100)

open tablelist

fetch tablelist into @tableName

while @@FETCH_STATUS = 0
begin
print @tableName
exec sp_helptrigger @tableName
fetch tablelist into @tableName
end

close tablelist
deallocate tablelist

0
 
markshifferCommented:
The triggers show up when you display the dependencies of a table. Try right clicking the table, going to All Tasks and then to display dependencies. You will see any triggers on the table in the left hand pane... Granted other stuff will be there too, but its quick and dirty...
0
 
LKSIUNGCommented:
Yes. Use sp_helptrigger <table_name>

Only one line. No need to write a program.
0
 
dmagAuthor Commented:
Thanks for the many answers. bbychkov's was the most elegant. :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now