Friday, August 17, 2012

modify sysjobs trigger to send alert

USE [msdb]
GO
/****** Object:  Trigger [dbo].[trig_sysjobs_insert_update]    Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON
  -- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view. 
  IF (EXISTS (SELECT *
            FROM inserted
           WHERE inserted.originating_server_id NOT IN
                    (SELECT v.originating_server_id
                     FROM sysoriginatingservers_view AS v)))
  BEGIN
   RAISERROR(14379, -1, -1, 'dbo.sysjobs')
   ROLLBACK TRANSACTION
    RETURN
  END
  else
  begin

    Declare @Body1 nvarchar(max)
    Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = 'palexander@csod.com',
    @body = @Body1,
    @subject = 'SQL Server Job Modification Alert'

  end
END

No comments: