Post reply

Follow up on previous post "How can I get alerted when jobs are deleted?"

  • June 13, 2008 at 1:23 pm

    #187888

    I recently posted an issue about how to get alerted when someone deletes a job. (see this thread: http://www.sqlservercentral.com/Forums/Topic510771-146-1.aspx)

    I found a solution. First I want to thank Animal Magic, homebrew01, and noeld for responding with some great ideas.

    I created a trigger on the sysjobs table that sends me an email when the job gets deleted. Here is the code...

    USE msdb

    GO

    CREATE TRIGGER JobDelete

    ON msdb.dbo.sysjobs

    AFTER DELETE

    AS

    declare @job nvarchar(128)

    Set @job = (select [name] from deleted)

    declare @body NVARCHAR(max)

    set @body = 'Notification of successful job deletion of'+' '+ @job+' '+'on'+' '+ @@servername

    exec msdb.dbo.sp_send_dbmail

    @profile_name = '[Database Mail Profile',

    @recipients = '',

    @importance = 'HIGH',

    @subject= 'Job Deleted',

    @body = @body

    go

    Thanks for your help

  • June 14, 2008 at 12:13 pm

    #828915

    I have to admit, I am quite suprised that SQL Sevrer 2005 allows you to do this. I guess they did not conver the msdb.dbo.* tables over to sys.* like most everything else.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 2 (of 2 total)

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