Retrieving code from a trigger in the database

I have inherited a database and with it a set of triggers that are 'apparently' installed. However I have a number of different 'versions' for a specific update trigger and it is not clear from the files which is the latest, hence I am unsure of what 'version' of the trigger is installed in the database itself.

I am trying to retrieve the trigger code from the databse so that i can correctly determine which one of my trigger files is the latest version.

I don't want to end up in a situation where an earlier version of the update trigger is installed as this could wreck the underlying data.

I have tried using the syscomments.text column that relates to the trigger but it only gives me a concatenated version of the sql code and not enough to determine which file version was used for the trigger.

Is there a way of retreiving the entire SQL code that was entered rather than just the first few lines?

Hi,

use the INFORMATION_SCHEMA.Routines for that.

SELECT Routine_definition
FROM INFORMATION_SCHEMA.Routines
Where Routine_Name = 'SomeTriggerName'

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||You can use sp_helptext to get the text for a trigger and other objects.
exec sp_helptext thenameofyourtrigger;|||

Hi,

this may sound dumb (first post here :) ), but why can't you just right-click the table in EM and go All Tasks -> Manage triggers? It gives you a nice syntax-highlighted view of all triggers assigned to a table.

|||Here is a simple code to get the trigger text in a nice format. One thing you need to do is to export the results to text. To do this right click in your sql pane (SQL 2005) and Results to ---> Results to text.

SELECT text FROM syscomments a INNER JOIN sysobjects b ON b.id = a.id and b.type = 'TR' AND b.name = <your trigger name in single quotes>

You can enhace the above to query to loop through all the triggers in a given table.

Hope this helps
Sri Kondeti