Agent Jobs

Create DDL Trigger For Create Database

Process Automation on the Metal Gears on Black Background.

Process Automation For Database Management.

Here’s some quick SQL logic to create a DDL Trigger which is fired whenever a new database is created. This isn’t too terribly hard to setup, but doesn’t hurt to put a post out there for you guys. This is incredibly useful whenever you’re setting up a new SQL Server environment, and you want to make sure that all the databases are configured within a uniform standard.

In this case… I have a trigger executing a Job simply called ‘SET DATABASE CONFIGS’, and within this job there is some SQL logic that sets recovery models etc. To take it a step further; my job will also notify the database group whenever new databases are added to this server environment.

Here’s the Trigger logic:

use master;
set nocount on
if exists(select 1 from sys.server_triggers where name = 'ddl_trig_run_job_on_create_database')
drop trigger ddl_trig_run_job_on_create_database on all server
go

create trigger ddl_trig_run_job_on_create_database on all server for create_database as
declare @new_database varchar(255)
set @new_database = cast(eventdata().query('/event_instance/databasename[1]/text()') as NVarchar(128))
exec msdb..sp_start_job @job_name = 'SET DATABASE CONFIGS';
go
Advertisements

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s