Recently, I had a client that wanted to be alerted whenever a new database was created or dropped on a specific SQL instance. I thought, no problem, I will just create a trigger to fire off and use DBmail to send the alert. It was actually quite easy to get it built, but I must admit, it took me a few minutes to find out where I created the trigger. Not everyone has created a server level DDL trigger; in fact, a lot of people have never even created a trigger before. So I thought maybe others might want to play with them too…
First step was to make sure DBmail was set up on my instance in question. You can follow the documentation on how to do that here. Once your DBmail is set up, it was as simple as writing a typical trigger with the exception of one small statement, ‘ON ALL SERVER’.
CREATE TRIGGER [trig_DDL_Alert] ON ALL SERVER
FOR CREATE_DATABASE, DROP_DATABASE
declare @DatabaseName VARCHAR(255)
declare @msgSubj varchar(max)
declare @msgTxt varchar(max)
SET @DatabaseName = (SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/DatabaseName)’, ‘VARCHAR(255)’))
SET @msgSubj = ‘DDL Update on ‘ + @@SERVERNAME + ‘ by ‘ + SUSER_SNAME()
SET @msgTxt =
@profile_name = ‘ @recipients = ”,
@body = @msgTxt,
@subject = @msgSubj
ENABLE TRIGGER trig_DDL_Alert ON ALL SERVER
It is important that you enable the trigger. Otherwise all you have done is created a trigger, but it will not fire off. Once you create and enable this trigger, you can try to create and drop a database to see what happens. You should get a few emails that look similar to the following.
Now, say you want to alter this trigger. You remember creating it in the Master DB, but you can’t seem to find it. A simple query will get you the info you need.
SELECT * FROM sys.server_triggers
You can also find all the server level triggers in SSMS by looking in the Server Objects > Triggers Folder on the Instance level.
There is plenty of DDL events that you can place triggers on. In case you wanted to monitor what your developers are creating on a sandbox server, you could set up triggers to alert in the creations for views, procedures and a lot of other items. You can see them all here.
Hopefully this helps you.