如何设置创建/删除作业时发送电子邮件警报的SQL Server 2008触发器
在许多小时的研究帮助下,我创建了一个触发器。启用/禁用SQL作业后,触发器当前会通过电子邮件发送给收件人。该扳机已经过测试,可以正常工作。
我想扩展触发器以捕获作业的创建和删除,但是我不确定如何。
这是我的代码:
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) = '[email protected]'
-- # 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
尝试将“ FOR UPDATE”更改为“ AFTER UPDATE,INSERT,DELETE”,这将运行触发器,然后进行插入并从表中删除
然后,您可以通过运行类似的命令来检查其更新,插入或删除
DECLARE @Type为varchar(1); SET @Type =(当存在(选择*从插入)和存在(选择*从删除)则'U'-则记录被更新。当存在(选择*从插入)则'I'-记录插入。当EXISTS(SELECT * FROM DELETED)THEN'D'-记录被删除。ELSE NULL
END)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句