我制作了一个SQL触发器,每当数据库中的任何存储过程发生更改时,它都会发送电子邮件警报。我可以在电子邮件中包含SP的名称吗?如何调用特定的SP名称以显示在电子邮件中?
这是我的代码:

ALTER TRIGGER [send_Email_to_User_ALTER]
ON DATABASE--msdb.dbo.[sp_send_dbmail]
AFTER ALTER_PROCEDURE
AS

--BEGIN
--SET NOCOUNT ON;

EXEC msdb.dbo.[sp_send_dbmail]
@profile_name = 'Test Alert',
@recipients = 'email@outlook.com',
@body = 'A STORED PROCEDURE HAS BEEN ALTERED.'  ,
@subject = 'ALERT System SP Change Notification';

最佳答案

试试这个:

ALTER TRIGGER [send_Email_to_User_ALTER]
ON DATABASE--msdb.dbo.[sp_send_dbmail]
AFTER ALTER_PROCEDURE
AS


--BEGIN
--SET NOCOUNT ON;
DECLARE @data XML
SET @data = EVENTDATA()
DECLARE @objName Nvarchar(max)
SET @objName = (SELECT  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)'))

DECLARE @body NVARCHAR(MAX)
SET @body = 'A STORED PROCEDURE HAS BEEN ALTERED.' + @objName

EXEC msdb.dbo.[sp_send_dbmail]
@profile_name = 'Test Alert',
@recipients = 'email@outlook.com',
@body = @body ,
@subject = 'ALERT System SP Change Notification';

关于mysql - 在ALTER_PROCEDURE DDL触发器中获取过程名称,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48453324/

10-11 19:31
查看更多