How to get the definition of a database trigger?

Question

when I run the following script in one of my databases:

SELECT * FROM SYS.triggers

when I try to find out about sys.triggers

Contains a row for each object that is a trigger, with a type of TR or TA. DML trigger names are schema-scoped and, therefore, are visible in sys.objects. DDL trigger names are scoped by the parent entity and are only visible in this view.

How then, do I get the database triggers definition?

score 4 · Answer 1 · edited Aug 09 '18 at 14:34

4

how about

select m.definition from sys.triggers t
inner join sys.objects o on t.object_id = o.object_id
inner join sys.sql_modules m on m.object_id = o.object_id

edited Aug 09 '18 at 14:34

Marcello Miorelli

17,274
53
180
320

answered Aug 09 '18 at 14:24

Randi Vertongen

16,593
4
36
64

score 2 · Accepted Answer · answered Aug 09 '18 at 14:25

You can use OBJECT_DEFINITION().

Shameless copy and paste example from the docs:

The following example returns the definition of a user-defined trigger, uAddress, in the Person schema. The built-in function OBJECT_ID is used to return the object ID of the trigger to the OBJECT_DEFINITION statement.

USE AdventureWorks2012;  
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.uAddress')) AS [Trigger Definition];   
GO

score 1 · Answer 3 · answered Aug 09 '18 at 14:32

I tried the object_definition and it seems to work.

SELECT 
             object_id=s.object_id
            ,parent_name  = CASE WHEN s.parent_id = 0 THEN 'DATABASE' ELSE QUOTENAME(OBJECT_SCHEMA_NAME(s.parent_id)) + '.' + QUOTENAME(OBJECT_NAME(s.parent_id)) END

            ,trigger_name = CASE WHEN s.parent_class = 1 
                                 THEN QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id))
                                 ELSE S.name
                             END

            ,s.is_disabled
            ,THE_DEFINITION = object_definition(s.object_id)
            FROM sys.triggers s

example of copying an pasting of one of the database trigger definitions:

create trigger tr_MStran_alterview 
on database 
for ALTER_VIEW as  

set ANSI_NULLS ON         
set ANSI_PADDING ON         
set ANSI_WARNINGS ON         
set ARITHABORT ON         
set CONCAT_NULL_YIELDS_NULL ON         
set NUMERIC_ROUNDABORT OFF         
set QUOTED_IDENTIFIER ON           
declare @EventData xml         
set @EventData=EventData()           
exec sys.sp_MStran_ddlrepl @EventData, 2

How to get the definition of a database trigger?

3 Answers3

Linked