Geek Answers Handbook

Is it possible for a trigger to detect the name of a stored procedure that changed data?

There are several stored procedures that are usually called by several different systems to serve multiple tables in our database. Some of them are automated, some are not.

One of the tables has a column whose number is sometimes turned off, and we do not know exactly when and why this happens. I want to put a trigger in the table so that I can see what changes and when, but it would also be useful to know which procedure initiated the modification.

Is it possible to get the name of a stored procedure from a trigger? If not, is there another way to tell what caused something to change? (I'm not talking about the user either, the username does not help in this case).

+9
source share
3 answers

you can try: CONTEXT_INFO

Here is an example using CONTEXT_INFO:

In each insert / delete / update procedure that you want to track, add the following:

 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 trigger part 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
+3
source

Our system already uses the CONTEXT_INFO variable for another purpose, so it is not available. I also tried the DBCC INPUTBUFFER solution , which almost worked. The return to the input buffer is that it returns only the external call procedure. Example: procA calls procB, which fires a trigger. The trigger fires DBCC INPUTBUFFER, which shows only procA. Since my trigger was looking for procB, this approach failed.

What I did in the meantime is to create a staging table. Now procA calls procB. procB inserts a row into the staging table, then fires the trigger. The trigger checks the staging table and finds the procB entry. Upon return, procB deletes its record from the staging table. This is a shell game, but it works. I would be interested in any feedback.

+1
source

I have not tried this, but @@ PROCID looks like it can return what you want.

-4
source

More articles:


All Articles