Viewing DDL Trigger Metadata by joining sys.server_sql_modules and sys.server_triggers : sys.server_sql_modules « System Tables Views « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. System Tables Views
3. sys.server_sql_modules

3>
4> SELECT t.name, m.definition
5> FROM sys.server_sql_modules m
6> INNER JOIN sys.server_triggers t ON
7> m.object_id = t.object_id
8> GO
name
         definition


------------------------------------------------------------------------------------------------------------------------
-------- ---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-------------------------
trg_RestrictNewLogins
         -- Disallow new Logins on the SQL instance
CREATE TRIGGER trg_RestrictNewLogins
ON ALL SERVER
FOR CREATE_LOGIN
AS
PRINT 'No login creations without DBA involvement.'
ROLLBACK

srv_trg_RestrictNewLogins


CREATE TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON
PRINT 'Your login creation is being monitored.'
INSERT YourDatabaseName.dbo.MyAudit
(EventData, DBUser)
VALUES (EVENTDATA(), USER)

1>