Trigger to notify when mssql job is disabled

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 28, 2017 at 9:07 am

  #1969736

  Yes, because IF @newEnabled <> @oldEnabled will always evaluate to False for an insert or delete,  That's because there will only be an Inserted or a Deleted table and therefore @newEnabled or @oldEnabled will be NULL, and comparing anything to NULL can't evaluate to True.  Have you read the article I linked to?

  John

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  November 28, 2017 at 11:15 am

  #1969774

  Here's what I'd suggest first. Make this simpler.

  Create a table in msdb or in your db. Just a couple fields, something like

  CREATE TABLE MyJobs
( JobID INT
, mydb VARCHAR(20)
, startdate DATETIME
)
  Create a trigger that tracks if you add, update, or delete multiple rows from this table. The trigger can audit data into a table or send an email, but test on a simpler way here. Once you have this, then adding fields and changing the tables to the msdb jobs tables becomes easier.

• goher2000

  SSCertifiable

  Points: 5479

  More actions

  November 28, 2017 at 11:39 am

  #1969777

  is this what you are looking for ?

  -- create table for fist run
  IF object_id('msdb..my_sysjobs') is null
  begin
  select * into msdb..my_sysjobs from msdb..sysjobs
  end
  -- send notfication
  select 'Job Deleted --> ' action,* from msdb..my_sysjobs where job_id not in (select job_id from msdb..sysjobs)
  -- send notfication

  select 'New Job Added --> ' action,* from msdb..sysjobs where job_id not in (select job_id from msdb..my_sysjobs)
  -- send notfication
  select 'Modfied Job --> ' action,* from msdb..sysjobs sj
  join msdb..my_sysjobs msj on sj.job_id=msj.job_id
  where sj.date_modified != msj.date_modified

  -- since notification has ben send out, get rid of data as it is no longer valid
  drop table msdb..my_sysjobs

  -- must creat table for next run
  IF object_id('msdb..my_sysjobs') is null
  begin
  select * into msdb..my_sysjobs from msdb..sysjobs
  end