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?)
LVL 1
dmagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.