DDL Trigger to Audit newly created databases, Send Mail when new database is created on the server

The below script send a mail to DBA whenever a new database was created / dropped by any user, It will help us to audit newly created and dropped databases.


CREATE TRIGGER Audit_Databases ON ALL SERVER for CREATE_DATABASE, DROP_DATABASE --for DDL_DATABASE_LEVEL_EVENTS
AS

BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @data XML
SET @data = EVENTDATA()
DECLARE @IP_ADDRESS VARCHAR(20)
DECLARE @SQL1 VARCHAR(100)
DECLARE @SQL2 VARCHAR(100)
DECLARE @SQL3 VARCHAR(100)

SET @SQL1 = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')
SET @SQL2 = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
SET @SQL3 = @@SERVERNAME

DECLARE @SQL VARCHAR(2000)
SET @SQL = 'EXEC MSDB..sp_send_dbmail @profile_name = ''Kalyan_Notifications'',
@recipients =''kalyan@yahoo.com'',
@SUBJECT = '' DATABASE ALERT'',
@body ='' '+ @SQL2+' DATABASE IS "'+@SQL1 + '" +  AT SERVER "' + @SQL3 +'"'''
--PRINT @SQL
EXEC (@SQL)
END