SQL JOB add/modified

Question

Post reply

SQL JOB add/modified

Shelley Allen

SSCrazy

Points: 2146
More actions
May 15, 2024 at 4:55 pm

#4421519

Is there a trigger to notify when a new SQL Agent job is created/modified?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 1

No, but you can make one and actually keep track of history. I have it show up in my "Morning Report".

Here's the table I created in my "DBA" database...

USE DBA;
GO
CREATE TABLE [dbo].[JobModifiedBy]
(
[Job_ID] [uniqueidentifier] NOT NULL,
[Job_Name] [sysname] NOT NULL,
[Date_Modified] [datetime] NOT NULL,
[Modified_By] [sysname] NOT NULL,
[Program_Name] [sysname] NOT NULL,
[Host_Name] [sysname] NOT NULL
)
GO
CREATE CLUSTERED INDEX [CI_By_Date_ModifiedJob_ID] ON [dbo].[JobModifiedBy]
(
[Date_Modified] ASC,
[Job_ID] ASC
)
GO

And here's the trigger I created in the MSDB database to capture the info.

USE msdb;
GO
 CREATE TRIGGER [dbo].[JobModifiedBy] ON [msdb].[dbo].[sysjobs] AFTER UPDATE, INSERT
/**********************************************************************************************************************
 Purpose:
 To keep a history of who changes jobs and the date/time they made the change.

 This trigger populates the "DBA.dbo.JobModifiedBy" table, which will be used by the Morning Job report.
----------------------------------------------------------------------------------------------------------------------- 
 Revision History:
 Rev 00 - 16 Sep 2016 - Jeff Moden - Initial creation and deployment.
**********************************************************************************************************************/
     AS
    SET NOCOUNT ON
;
     IF UPDATE (date_modified)
  BEGIN
        --===== Insert data from the "inserted" logical table for both INSERT and UPDATE.
         INSERT INTO DBA.dbo.JobModifiedBy
                (
                 Job_ID
                ,Job_Name
                ,Date_Modified
                ,Modified_By
                ,[Program_Name]
                ,[Host_Name]
                )
         SELECT  JOB_ID
                ,[name]
                ,Date_Modified
                ,suser_name()
                ,PROGRAM_NAME()
                ,HOST_NAME()
           FROM inserted
        ;
    END
;
GO

If you need to, you could modify the trigger to fire when DELETEs occur, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shelley Allen SSCrazy Points: 2146 More actions · Answer 2

Shelley Allen

SSCrazy

Points: 2146

May 15, 2024 at 6:15 pm

#4421542

Thank you!

Shelley Allen SSCrazy Points: 2146 More actions · Answer 3

If I wanted this to email me also when the change was made, how would I do that?

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 4

Shelley Allen wrote:

If I wanted this to email me also when the change was made, how would I do that?

Setup a stored procedure to send the email and have the trigger fire the stored procedure. Email is asynchronous so even if email is toasted, it won't cause an issue.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)