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

本文介绍了触发器是否可以找到修改数据的存储过程的名称?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有一些存储过程通常会被一些不同的系统调用，以对我们数据库中的一些表进行维护.有些是自动化的，有些不是.

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

这是检索值的触发器部分:

here is the portion of the trigger to retrieve the 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

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