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

Your trigger works fine for me. Of course in this exact script, you're dropping the trigger (if it exists), then creating the trigger, then dropping it again. The next question is how to get this to send you an e-mail; [there are plenty of answers for that](http://stackoverflow.com/search?tab=votes&q=%5bsql-server%5d%20send%20e-mail). If you add code that tries to send an e-mail and you can't get it working, come back here with specific questions about that code. — Aaron Bertrand, Jan 22 '14 at 14:53

score 0 · Answer 1 · answered Jan 22 '14 at 15:01

0

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

answered Jan 22 '14 at 15:01

StuartLC

104,537
17
209
285

hi StuartLC how to create Logon triggers – Anjali Apr 22 '14 at 07:19
@Anjali - Welcome to Stackoverflow. Instead of asking a question on an old post, you need to post a new question (after checking to ensure there are no existing duplicates of your question) - Old questions like this one do not get much attention. – StuartLC Apr 22 '14 at 07:44

score 0 · Answer 2 · answered Dec 13 '18 at 12:53

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

DDL Trigger with example

2 Answers2