How do you determine what procedure performed an update on a table?

Question

Post reply

How do you determine what procedure performed an update on a table?

WayneS

SSC Guru

Points: 95460
More actions
March 9, 2010 at 12:41 pm

#139309

I'm trying to track down how a specific column in a table is being updated. I'm looking into adding a trigger on the table, and inserting before/after data into a logging (audit) table.
I need to also track what procedure is performing the update. If I use Object_Name(@@PROCID), I end up with the name of the trigger, not the calling procedure.
Is it possible to get the procedure that caused a trigger to fire from within the trigger?
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I believe the answer is no. There is an active MS Connect item requesting it at:

http://connect.microsoft.com/SQLServer/feedback/details/124537/provide-function-to-retrieve-the-entire-call-stack

Which further indicates it isn't available..

CEWII

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 2

In the absence of the smart solution have you considered an ugly process of a trigger updating a logging table everytime the column gets updated, and running a profiler tracking all stored procs that are starting. When the column get logged (throw in a trace stop?) and then check which stored procs were running at the time?

Like I say...Ugly. I'm sure you've probably thought of something like this. Also it may prove time consuming trudging through the stored procs that were running on a very large and active database.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Off the top of my head that sounds like an ok solution. I don't have a better one. Ugly, but functional..

If I would add something I would probably build the trace internally and not use profiler, using the sprocs directly, this is considered a server side trace..

CEWII

WayneS SSC Guru Points: 95460 More actions · Answer 4

Shark Energy (3/9/2010)
In the absence of the smart solution have you considered an ugly process of a trigger updating a logging table everytime the column gets updated, and running a profiler tracking all stored procs that are starting. When the column get logged (throw in a trace stop?) and then check which stored procs were running at the time?
Like I say...Ugly. I'm sure you've probably thought of something like this. Also it may prove time consuming trudging through the stored procs that were running on a very large and active database.

While this sounds doable, it is definitely uglier than I'd like to do at the time.

The connect item definitely looks better...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

I need to also track what procedure is performing the update

Assuming (and you know what that makes of me = SELECT SUBSTRING('assuming',1,3))

The following code will return all stored procedures that access the table in question.

CREATE TABLE #Temp(TableName VARCHAR(50), Spname VARCHAR(3000))

INSERT INTO #Temp

SELECT so.name, sob.name as 'Stored Procedure name' FROM sysobjects so

LEFT OUTER JOIN (sysobjects sob left outer join sysdepends on sob.id = sysdepends.id)

on sysdepends.depid = so.id

WHERE so.xtype = 'u' AND sob.xtype = 'p'AND NOT so.name = 'dtproperties'

AND so.name = 'yourtablesname'

From the above (with a bit more code) this will return the text of each stored procedure that contains the "UPDATE" command

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION

From INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE'

and ROUTINE_NAME = 'yourroutinenamefrom#temp ' AND ROUTINE_DEFINITION LIKE '%update%'

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]