TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.

Words: 306

Time to read: ~ 1.5 minutes

TSQL Tuesday is around again! This monthly blog party started by Adam Machanic ( blog | twitter ) is still going strong. This month we have Steve Jones ( blog | twitter ) and the topic is all to do with Triggers!

Now, my normal attitude with regard to triggers tends to run to the negative. Which is horrible, because triggers are just like any other tool; neutral by themselves and only good or bad based on how we use them.

So, with that being said, I’ve forced myself to think of a positive use for them. So here is a time when I’ve used triggers for a “good” cause and used them to get some visibility on when new databases are created.

Code:

The above code is split into 3 regions:

1. The Server Trigger code,
2. a Simple Database creation statement, and
3. a more advanced option where we turn on database containment and change the two_digit_year_cutoff date (basically more code than just CREATE DATABASE)

Want to know why I chose two_digit_year_cutoff? Well, I’ve just read about a nice, little “got’cha” problem with it thanks to Cláudio Silva ( blog | twitter ).

Server Trigger

This Server Trigger captures the

• Server name,
• Database name,
• Creation time,
• Login name,
• and the command used to create the database.

When the script is ran as a whole, the results are:

Simple enough to use sp_send_dbmail to send an email when ever this is run!

Now, when you’re asked to restore a database from backups, you don’t have to go “Hey! Whoa, whoa, whoa! When dahell did that get there?!”

Instead, you can focus on why people feel to need to create databases without you and you can address that while making sure everything is created to your standard.

I’ll take a trigger so that I can have that functionality anyday.