SQL Stuff

If Not Exists Create Trigger For Job Alerts

Here’s some SQL logic that checks for the Trigger [trig_check_for_job_failure]. If it does not exist it will automatically create it. Again; what this trigger does is simply executes the Job: Send SQL JOB Alerts which was created on a former post. All the logic is there to create the Alerts, Trigger, and Job, but… In case you wanted something to check on the trigger first; I’ve included the logic below. Hope it’s helpful.

use msdb;
set nocount on

if not exists (select name from msdb..sysobjects where name = 'trig_check_for_job_failure' and type = 'tr') exec dbo.sp_executesql @statement = N'
create trigger [dbo].[trig_check_for_job_failure] on [dbo].[sysjobhistory] after insert
set nocount on
declare	@is_fail	int
set	@is_fail	= (select case when [message] like ''%The step failed%'' then 1 else 0 end from msdb..sysjobhistory where instance_id in (select max(instance_id) from [msdb]..[sysjobhistory])) if	@is_fail	= 1
exec msdb.dbo.sp_start_job @job_name = ''SEND SQL JOB ALERTS'' end

Categories: SQL Stuff

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