How to Create Server Level Trigger in SQL Server - SQL Server DBA Tutorial

In this video tutorial you will learn how to create SQL Server Level Trigger in SQL Server using T-SQL script. Script will teach you how to create Server Level Trigger, how to store trigger output in a table, how to send email based on different events occurred on SQL Server instance such as creating database, dropping database etc. It also illustrates how to use DBMail in SQL Server to send alerts to your email.


USE [master]
GO 
/****** Object:  DdlTrigger [dbcreate]    Script Date: 3/9/2015 2:24:39 PM ******/ 
SET ANSI_NULLS ON
 GO 
SET 
QUOTED_IDENTIFIER ON 
GO 
CREATE TRIGGER [dbcreate] 
ON ALL SERVER
 FOR CREATE_DATABASE, Drop_database 
AS
BEGIN
                DECLARE @Eventdata XML
                SET @Eventdata = EVENTDATA()

                     IF EXISTS(SELECT 1 FROM MASTER.information_schema.tables WHERE table_name = 'PermissionAudit')
                     BEGIN
                           INSERT INTO MASTER.dbo.PermissionAudit
                                  (EventType,EventData, ServerLogin,TSQLText)
                                  VALUES (@Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nVarChar(100)'),
                                                @Eventdata, SYSTEM_USER,
                                                @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nVarChar(2000)' ))
                     END

   
DECLARE @Table NVARCHAR(MAX) 
DECLARE @body NVARCHAR(MAX) 
SET @Table = CAST((SELECT [EventType]   AS 'td',
                          '',
                          [EventData]   AS 'td',
                          '',
                          [ServerLogin] AS 'td',
                          '',
                          [TSQLText]    AS 'td'
                   FROM   MASTER.dbo.PermissionAudit
                   FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX)) 
SET @body ='<html><body><H2>Audit Information From Trigger</H2>
<table border = 1> 
<tr>
<th> EventType </th> <th> EventData </th> <th> ServerLogin </th> <th> TSQLText </th> <th>' 
SET @body = @body + @Table + '</table></body></html>' 
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Test',
  @body = @body,
  @body_format ='HTML',
  @recipients = 'youremailaddress@domain.com',
  @subject = 'Audit Database Create or Drop';


    END
TRUNCATE TABLE MASTER.dbo.PermissionAudit
GO
ENABLE TRIGGER [dbcreate] ON ALL SERVER

GO


How to Create Server Level Trigger in SQL Server