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
Very good explainatin. Rawoof Shaik, India
ReplyDelete