USE msdb
GO
IF OBJECT_ID('[tr_SysJobs_enabled]') IS NOT NULL
DROP TRIGGER [dbo].[tr_SysJobs_enabled]
GO
CREATE TRIGGER [dbo].[tr_SysJobs_enabled] ON [dbo].[sysjobs]
FOR UPDATE
AS
----------------------------------------------------------------------------
-- Object Type : Trigger
-- Object Name : msdb..tr_SysJobs_enabled
-- Description : trigger to email DBA team when a job is enabled or disabled
-- Author : www.mssqltips.com
-- Date : July 2009
----------------------------------------------------------------------------
SET NOCOUNT ON
IF NOT UPDATE(Enabled)
RETURN
DECLARE @Bodytext VARCHAR(MAX),
@SubjectText VARCHAR(MAX) ;
WITH jobs
AS (SELECT CASE WHEN i.Enabled = 1
AND d.Enabled = 0 THEN 'Enabled'
WHEN i.Enabled = 0
AND d.Enabled = 1 THEN 'Disabled'
WHEN i.Enabled IS NULL THEN 'Deleted'
WHEN d.Enabled IS NULL THEN 'Inserted'
END AS ACTION,
COALESCE(i.Name, d.NAME, '') AS JobName,
ISNULL(i.job_id, d.job_id) AS JobId
FROM Inserted i
FULL OUTER JOIN Deleted d ON i.job_id = d.job_id)
SELECT @subjecttext = 'SQL Agent Job ' + ACTION,
@bodytext = 'User: ' + ISNULL(SYSTEM_USER, '') + ', Host ' + ISNULL(HOST_NAME(), '') + ' ' + ISNULL(ACTION, '') + ' SQL Job [' + JobName + '] {'
+ CAST(JobId AS VARCHAR(100)) + '} at ' + CONVERT(VARCHAR(20), GETDATE(), 100) + +ISNULL('
******************** Job Steps ********************
' + js.JobStepDetail, '')
FROM jobs j
OUTER APPLY (SELECT Util.dbo.StringConcat(' Step Id: ' + ISNULL(CAST(js.step_id AS VARCHAR), '') + ', Name: ' + ISNULL(js.step_name, '')
+ ', subsystem: ' + ISNULL(js.subsystem, '') + ISNULL(', database: ' + js.database_name, ''), '
') AS JobStepDetail
FROM sysjobsteps js (NOLOCK)
WHERE js.job_id = j.JobId) js
WHERE ACTION IS NOT NULL
IF @@ROWCOUNT <> 1
RETURN
-- send out alert email
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'diablosupport.facl.fl@corelogic.com', --<<< insert your team email here
--@recipients = 'CoreStoreSupport.FACL.FL@Corelogic.com', --<<< insert your team email here
@body = @bodytext,
@subject = @subjecttext
GO