How to have SQL Server 2008 trigger when a job is created/deleted

Question

How to set up a SQL Server 2008 Trigger that sends an Email Alert when a Job is Created/Deleted

I have created a trigger, with the help of many hours of research. The trigger currently emails out to a recipient when a SQL job has been enabled/disabled. The trigger has been tested and works without fail.

I would like to extend the trigger out to capture the creation and deletion of jobs as well, but I am not sure how.

Here is the code that I have:

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[jobChecker] 
ON sysjobs 
FOR UPDATE AS 
SET NOCOUNT ON

-- # DECLARE VARIABLES # --

DECLARE @username VARCHAR(50), 
		@hostName VARCHAR(50), 
		@jobName VARCHAR(100),
		@newEnabled INT, 
		@oldEnabled INT,
		@jobCreated INT,
		@bodyText VARCHAR(200), 
		@subjectText VARCHAR(200),
		@servername VARCHAR(50),
		@profileName VARCHAR(50) = 'profile_name',
		@recipients VARCHAR(500) = 'example@domain.com'

-- # SET VARIABLES # --

SELECT @username = SYSTEM_USER
SELECT @hostName = HOST_NAME()
SELECT @servername = @@servername
SELECT @newEnabled = ENABLED FROM Inserted
SELECT @oldEnabled = ENABLED FROM Deleted
SELECT @jobName = Name FROM Inserted 

-- # CHECK FOR ENABLED/DISABLED # --

IF @newEnabled <> @oldEnabled 
BEGIN 

  IF @newEnabled = 1 
  BEGIN 
    SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
    SET @subjectText = @servername + ' : [' + @jobName + '] has been ENABLED'
  END 

  IF @newEnabled = 0 
  BEGIN 
    SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
    SET @subjectText = @servername+' : [' + @jobName + '] has been DISABLED' 
  END 

  SET @subjectText = 'SQL Job on ' + @subjectText 

-- # SEND EMAIL # --

  EXEC msdb.dbo.sp_send_dbmail 
  @profile_name = @profileName,
  @recipients = @recipients,
  @body = @bodyText, 
  @subject = @subjectText 

END

Answer 1

try change the "FOR UPDATE" to "AFTER UPDATE, INSERT, DELETE" this should run the trigger then for inserts and deleted from the table

you can then check if its an update , insert or delete by running something like

DECLARE @Type as varchar(1); SET @Type = ( CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'U' -- then the record was updated. WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I' --the record was inserted. WHEN EXISTS(SELECT * FROM DELETED) THEN 'D' --the record was delete. ELSE NULL
END)