⟩ How To Get the Definition of a Trigger Back?
If you want get the definition of an existing trigger back from the SQL Server, you can use the catalog view called sys.sql_modules, which stores definitions of views, stored procedures, and triggers.
The sys.sql_modules holds trigger definitions identifiable by the object id of each trigger. The tutorial exercise below shows you how to retrieve the definition of trigger, "dml_message" by joining sys.sql_modules and sys.triggers:
USE GlobalGuideLineDatabase;
GO
SELECT m.definition
FROM sys.sql_modules m, sys.triggers t
WHERE m.object_id = t.object_id
AND t.name = 'dml_message';
GO
definition
-------------------------------------------------
CREATE TRIGGER dml_message ON ggl_users
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE());
PRINT 'Records are inserted, updated,'
+ ' or deleted in ggl_users';
(1 row(s) affected)
