DDL Trigger with example

Question

I want to create a trigger which emails me the name of the databases created in a server. Whenever a DB is created I should get an email. Please help.

I have tried the following code just to return a print statement but that is not working:

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

Answer 1

Your example is the one on MSDN. Here's an example of just auditing the DB's which have been created:

CREATE TABLE master.dbo.NewDB
(
    DbName NVARCHAR(200),
    DateCreated DATETIME CONSTRAINT DF_NewDBCreated DEFAULT(CURRENT_TIMESTAMP)
)
GO

CREATE TRIGGER ddl_trig_database 
    ON ALL SERVER 
    FOR CREATE_DATABASE 
AS 
    INSERT INTO master.dbo.NewDB(DbName)
        SELECT EVENTDATA()
    .value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO


CREATE DATABASE DB1
GO
CREATE DATABASE DB2
GO
SELECT * FROM master.dbo.NewDB;

...

   CREATE DATABASE DB1  2014-01-22 16:57:43.280    
   CREATE DATABASE DB2  2014-01-22 16:57:47.250

Answer 2

Your trigger code work fine except you need to either comment the drop trigger code or delete the drop trigger code as shown in below screenshot.

Print command in DDL trigger

Read more about SQL trigger on

http://www.techmixing.com/2018/12/sql-triggers-introduction.html