I ran into this strange situation where I wanted to get Trigger Definition(the SQL content) of a specific trigger from a database. The following SQL query can used to retrieve the trigger definition programitically.
SELECT
DB_NAME() AS DataBaseName,
dbo.SysObjects.Name AS TriggerName,
dbo.sysComments.Text AS SqlContent
FROM
dbo.SysObjects INNER JOIN
dbo.sysComments ON
dbo.SysObjects.ID = dbo.sysComments.ID
WHERE
(dbo.SysObjects.xType = 'TR')
AND
dbo.SysObjects.Name LIKE 'YourTriggerName'
