触发器是否能够查找修改数据的存储过程名称？

Question

我们的数据库中有一些表需要定期进行维护，这些维护操作由不同的系统调用一些存储过程完成，其中某些是自动化的。

其中一个表格的一列数据有时会出现错误，我们不确定发生错误的时间和原因。我想在该表上放置一个触发器，以便查看何时发生了更改，但也很有帮助知道哪个存储过程启动了修改。

是否可以从触发器获取存储过程的名称？如果不能，是否有其他方法可以确定是什么导致了修改？（我不是指用户，因为用户的名称在这种情况下没有帮助）。

- Brandon

---

我认为这是不可能的，因为它可能不是运行的存储过程，而只是一个简单的批处理。我可能是错误的，但在您的情况下，您应该考虑一些获取涉及特定表的最近批次列表的方法，这本质上就是监视器所做的事情。 - BeemerGuy

Answer 1

您可以尝试使用：CONTEXT_INFO

以下是一个使用 CONTEXT_INFO 的示例：

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

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

- KM.

---

@KM，感谢您的建议，我会尝试一下，但我不确定我的团队是否会因为我需要修改每个存储过程进行临时调试而感到高兴:P。 - Brandon

你会更高兴不去修复这个 bug 吗？触发器有时候可能很麻烦。 - KM.

APP_NAME()有助于追踪不同的系统。 - user423430

Answer 2

我们的系统已经在使用CONTEXT_INFO变量做其他用途，因此不可用。我还尝试了DBCC INPUTBUFFER solution，它几乎可以工作。但是inputbuffer的缺点是它只返回外部调用过程。例如：procA调用procB，然后触发器被激活。触发器运行DBCC INPUTBUFFER，只显示procA。由于我的触发器正在寻找procB，所以这种方法失败了。

与此同时，我创建了一个临时表。现在procA调用procB。procB向临时表插入一行，然后触发器被激活。触发器检查临时表并找到procB条目。在返回时，procB从临时表中删除其条目。这是一个壳游戏，但它有效。我很想听听任何反馈。

- Andy

---

遗憾的是，这是解决 SQL Server 中任何过程/触发器调用堆栈不足的最佳方法。即使 Oracle 也有这个功能！我工作的第三方数据库也在各处使用 CONTEXT_INFO。现在我唯一能提供的新建议是，在 SS2016 中，您可以使用 MEMORY_OPTIMIZED 表将临时调用堆栈信息存储在内存中，或者使用 SESSION_CONTEXT 跟踪 仅 最近的 @@PROCID 热点。注意：从 SS2014-SP2 开始，您还可以使用 sys.dm_exec_input_buffer()，而无需声明表变量并调用动态 SQL 来执行 DBCC INPUTBUFFER。 - MikeTeeVee

Answer 3

我没有尝试过这个，但是@@PROCID看起来可能会返回你想要的结果。

- HLGEM

---

我认为，一旦你进入触发器代码，这将返回触发器本身的ID。 - Joe Stefanelli

@@procid（或object_name（@@procid））在表中作为列默认值非常有用，以了解插入来自哪里。 - bwperrin