SQL Server FAQ - sys.sql_modules - Getting Trigger Definitions Back

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

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 FyiCenterData;
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 fyi_users
AFTER INSERT, UPDATE, DELETE 
AS
  PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE());
  PRINT 'Records are inserted, updated,'
    + ' or deleted in fyi_users';
(1 row(s) affected)

(Continued on next topic...)