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

I see your hidden database table trigger and raise you a more hidden server one!

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.


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:

new lines = multiple spaces :/

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.

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

3 thoughts on “TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s