Triggers

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  November 2, 2005 at 9:10 am

  #169573

  HI

  How can I list out all the triggers i have on a database instead of cheking each n every table.

• Lye Beng Tat

  Newbie

  Points: 1

  More actions

  November 3, 2005 at 12:01 am

  #602040

  Hi,

  You could list out those triggers from sysobjects table where xtype = 'TR'  

• Linata

  Ten Centuries

  Points: 1370

  More actions

  November 3, 2005 at 2:40 am

  #602061

  create table #test (trgname varchar(100), owner varchar(40),isupdate bit,isdelelete bit,isinsert bit,isafter bit, isinsteafof bit)

  exec sp_msforeachtable  'insert into #test exec sp_helptrigger ''?'''

  select * from #test

  go

  drop table #test

• Lowell

  SSC Guru

  Points: 323516

  More actions

  November 3, 2005 at 9:15 am

  #602166

  and here's another method that i use myself; i need to find all trigers and their trigger body if it exists:

  select parent_objects.name,  

    trigger_sysobjects.name,  

    syscomments.text  

    FROM sysobjects trigger_sysobjects  

    INNER JOIN sysobjects parent_objects on parent_objects.id=  trigger_sysobjects.parent_obj  

    INNER JOIN syscomments on trigger_sysobjects.id=syscomments.id  

    WHERE trigger_sysobjects.xtype='TR' 

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!