1

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

SELECT * FROM SYS.triggers

enter image description here

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?

1

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 
3

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
  • that works fine for the normal DML triggers, but it does not include the DDL triggers. Nice try though, deserves a +1 – Marcello Miorelli Aug 9 '18 at 14:26
1

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.