触发器是否有可能找到修改了数据的存储过程的名称？ [英] Is it possible for a trigger to find the name of the stored procedure that modified data?

问题描述

一些存储过程通常由一些不同的系统调用，以对数据库中的一些表进行维护。

There are a few stored procedures that routinely get called by a few different systems to do maintenance on a few tables in our database. Some are automated, some aren't.

其中一个表有一个列，其中的数字有时是关闭的，我们不确定何时或为什么这样做正在发生。我想在桌子上放一个触发器，这样我可以看到正在更改的内容以及何时更改，但是了解哪个过程启动了修改也将很有帮助。

One of the tables has a column where the number is sometimes off, and we don't know for sure when or why this is happening. I want to put a trigger on the table so I can see what is being changed and when, but it'd also be helpful to know which procedure initiated the modification.

是否可以从触发器获取存储过程的名称？如果不是，是否有其他方法可以说明是什么导致某些内容被修改？ （我也不是在谈论用户，在这种情况下，用户名也无济于事。）

Is it possible to get the name of the stored procedure from the trigger? If not, is there any other way to tell what caused something to be modified? (I'm not talking about the user either, the name of the user doesn't help in this case).

推荐答案

您可以尝试： CONTEXT_INFO

这是一个CONTEXT_INFO用法示例：

here is a CONTEXT_INFO usage example:

在执行要跟踪的插入/删除/更新的每个过程中，添加以下内容：

in every procedure doing the insert/delete/update that you want to track, add this:

DECLARE @string        varchar(128)
       ,@CONTEXT_INFO  varbinary(128)
SET @string=ISNULL(OBJECT_NAME(@@PROCID),'none')
SET @CONTEXT_INFO =cast('Procedure='+@string+REPLICATE(' ',128) as varbinary(128))
SET CONTEXT_INFO @CONTEXT_INFO

--do insert/delete/update that will fire the trigger

SET CONTEXT_INFO 0x0 --clears out the CONTEXT_INFO value

这是触发以检索值：

DECLARE @string         varchar(128)
       ,@sCONTEXT_INFO  varchar(128)
SELECT @sCONTEXT_INFO=CAST(CONTEXT_INFO() AS VARCHAR) FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID

IF LEFT(@sCONTEXT_INFO,9)='Procedure'
BEGIN
    SET @string=RIGHT(RTRIM(@sCONTEXT_INFO),LEN(RTRIM(@sCONTEXT_INFO))-10)
END
ELSE
BEGIN --optional failure code
    RAISERROR('string was not specified',16,1)
    ROLLBACK TRAN
    RETURN
END

..use the @string

这篇关于触发器是否有可能找到修改了数据的存储过程的名称？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！