SQL Server/T-SQL Tutorial/System Tables Views/sys.triggers

Viewing DDL Trigger Metadata by joining sys.triggers and sys.sql_modules

4>
5> SELECT t.name, m.Definition
6> FROM sys.triggers AS t
7> INNER JOIN sys.sql_modules m ON
8> t.object_id = m.object_id
9> WHERE t.parent_class_desc = "DATABASE"
10> GO
name
         Definition

------------------------------------------------------------------------------------------------------------------------
-------- ---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-------------------------
db_trg
CREATE TRIGGER db_trg
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
SET NOCOUNT ON
INSERT dbo.MyAudit
(EventData, DBUser)
VALUES (EVENTDATA(), USER)

Viewing DDL Trigger Metadata by querying sys.triggers

3>
4> SELECT name TriggerNM, is_disabled
5> FROM sys.triggers
6> WHERE parent_class_desc = "DATABASE"
7> ORDER BY OBJECT_NAME(parent_id), name
8> GO
TriggerNM
         is_disabled
------------------------------------------------------------------------------------------------------------------------
-------- -----------
db_trg
                   0
1>

Viewing DML Trigger Metadata: Show the DML triggers in the current database

3>
4> SELECT OBJECT_NAME(parent_id) Table_or_ViewNM,
5> name TriggerNM, is_instead_of_trigger, is_disabled
6> FROM sys.triggers
7> WHERE parent_class_desc = "OBJECT_OR_COLUMN"
8> ORDER BY OBJECT_NAME(parent_id), name
9> GO
Table_or_ViewNM
         TriggerNM
                  is_instead_of_trigger is_disabled
------------------------------------------------------------------------------------------------------------------------
-------- ---------------------------------------------------------------------------------------------------------------
----------------- --------------------- -----------
1>