Thursday, November 26, 2015

Trigger for new DB creation in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trig_NewDBAlert]
ON ALL SERVER
FOR CREATE_DATABASE
AS
                declare @subj varchar(75)
                declare @bod nvarchar(max)
               
                SELECT @subj = cast(SERVERPROPERTY('ServerName') as varchar) + ' New Datbase Created (' + SYSTEM_USER +')'
                SELECT @bod = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
                EXEC msdb..sp_send_dbmail @profile_name = 'Mssqldba',
  @recipients = N'adil9025@gmail.com', @subject = @subj,@body = @bod
               
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [trig_NewDBAlert] ON ALL SERVER
GO



No comments:

Post a Comment