执行更新操作时如何从触发器获取存储过程名称? [英] How to get the stored procedure name from a trigger when an update operation was performed?

问题描述

可能的重复:
触发器是否可以找到修改数据的存储过程的名称?

我有一个带有触发器(更新时)的表.执行触发器时，我想知道更新相关表的存储过程的名称.

I have a table with a trigger (on update) on it. When the trigger is executed I would like to know the name of the stored procecure which updated the table in question.

推荐答案

这并不总是 100% 可靠，它有时会捕获外部过程调用，即使该过程称为内部过程.但是您至少可以了解用户调用的内容，最终触发了它们.

This won't always be 100% reliable, and it sometimes will capture the outer procedure call even if that procedure called an inner one. But you can at least get some idea of what the user called that ended them up in the trigger.

ALTER TRIGGER dbo.whatever
ON dbo.something
FOR UPDATE
AS
BEGIN
    ... other trigger logic

    DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

    CREATE TABLE #inputbuffer 
    (
      EventType nvarchar(30), 
      Parameters int, 
      EventInfo nvarchar(255)
    )

    SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

    INSERT INTO #inputbuffer 
    EXEC (@ExecStr)

    SET @Qry = (SELECT EventInfo FROM #inputbuffer)

    SELECT @Qry AS 'Query that fired the trigger', 
     SYSTEM_USER as LoginName, 
     USER AS UserName, 
     CURRENT_TIMESTAMP AS CurrentTime

   -- of course you can store this somewhere instead of select
END

从 Vyas K 窃取:http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

Stolen from Vyas K: http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

您可能还想查看这个问题，其中有一个与使用 sys.dm_exec_query_stats 相关的答案 - 您可以使用 sys.dm_exec_sql_text 公开的对象 ID 追溯到过程名称，并且您可以使用 GETDATE() 作为最近"的指南来限制最近的调用.还有一个使用扩展事件来做这件事的冗长示例.

You may also want to check out this question, which has an answer relating to using sys.dm_exec_query_stats - you can track back to procedure name using the object id exposed by sys.dm_exec_sql_text, and you can limit to very recent invocations using GETDATE() as a guide for "recent." There is also a lengthy example of using Extended Events to do it.

创建触发器来记录影响表的 SQL?

或者这些使用 CONTEXT_INFO 的(但这需要更改更新表的所有存储过程):

Or these ones which use CONTEXT_INFO (but this requires changing all of the stored procedures that update the table):

查找触发触发器的查询

触发器是否可以找到修改数据的存储过程的名称?

最后，您可以查看这些帖子以获得一些想法:

Finally, you may check out these posts for some ideas:

http://www.thecodepage.com/post/TIP-A-Call-Stack-in-SQL-Server.aspx

这篇关于执行更新操作时如何从触发器获取存储过程名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！