Triggers

[rmatthew]

Anybody have a bit of SQL that will list the triggers on a table along with the trigger's name and the text of the trigger?

[Nerseus]

Here ya go, just typed this up so it may not be 100%:

select [text] from syscomments where id in
(
select id from sysobjects 
where xtype = 'tr' -- trigger
and parent_obj = 
(
	select id from sysobjects where name = 'TableOfInterest' and xtype = 'u' -- user table
)
)

 

The inner select uses sysobjects to get the id of the table you're interested in.

The next select uses sysobjects again, to get the triggers for that table (using parent_obj).

The outer select gets the text.

 

If you've turned on the SQL Server option to hide the text, you're out of luck. SQL can be made to only store the compiled objects.

 

-ner