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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — region Server Trigger | |
| IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = N'NewDatabase') DROP TRIGGER NewDatabase ON ALL SERVER; | |
| GO | |
| CREATE TRIGGER NewDatabase | |
| ON ALL SERVER | |
| FOR CREATE_DATABASE | |
| AS | |
| SELECT EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(256)') AS ServerName, | |
| EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') AS DatabaseName, | |
| EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS CreateTime, | |
| EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)') AS CreatedBy, | |
| EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') AS CreationCommand; | |
| GO | |
| — endregion | |
| — region Simple Database | |
| IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'NewOne') DROP DATABASE NewOne; | |
| GO | |
| CREATE DATABASE NewOne; | |
| GO | |
| — endregion | |
| — region Database with options. | |
| IF (SELECT value_in_use FROM sys.configurations WHERE name = N'contained database authentication') <> 1 BEGIN | |
| EXEC sp_configure @configname = N'contained database authentication', @configvalue = 1; | |
| RECONFIGURE | |
| END | |
| IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'NewYear') DROP DATABASE NewYear; | |
| GO | |
| CREATE DATABASE NewYear | |
| CONTAINMENT = PARTIAL | |
| WITH TWO_DIGIT_YEAR_CUTOFF = 2099; | |
| GO | |
| — endregion |
The above code is split into 3 regions:
- The Server Trigger code,
- a Simple Database creation statement, and
- 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.
This is a fantastic use of a trigger! Nicely done sir =)