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:
Post a Comment