Triggers

[rmatthew]

Anybody have a bit of SQL that will list the triggers on a table along with the triggers 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 youre 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 youve turned on the SQL Server option to hide the text, youre out of luck. SQL can be made to only store the compiled objects.

-ner