Find procedure name in trigger

Question

Post reply

Find procedure name in trigger

spaghettidba

SSC Guru

Points: 105732
More actions
July 9, 2010 at 3:45 am

#143990

Hi there!
I want to find the name of the procedure that updates a table for logging purposes.
I created a trigger and tried to get that information, but I can't find exactly what I'm looking for.
Typically things work this way: the client application calls a stored procedure, let's say "sp_A", then sp_A calls other procedures (sp_B, sp_C, sp_D) that update the table. I would like to trap the second procedure name (let's say "sp_C") in my trigger, but I can't find a way.
DBCC INPUTBUFFER returns the original query text, e.g. "EXEC sp_A"
sys.dm_exec_requests / sys.dm_exec_query_text returns the trigger text
sys.dm_exec_requests includes a "nest_level" column that displays current nest level correctly, but doesn't give any pointer to parent request. Is there a way to navigate the requests from child to parent?
-- Gianluca Sartori

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

UPDATE: looks like there's no way to do what I'm after.

Adam Machanic opened a connect entry for that...

Any ideas?

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You could always have every proc check for existence of a temp table, if not exists create it, then insert the name of the proc into that temp table. In the trigger you could then read back over that temp table's rows. Manual work, but should work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Thanks Gail. It's not exactly what I was looking for, but you gave me a great idea!

For logging purposes every stored procedure in my db starts with a line like this:

INSERT INTO ProcedureAudit

SELECT db_name(), schema_name(), OBJECT_NAME(@@procid)

ProcedureAudit is a view with a TRIGGER INSTEAD OF INSERT and it does an "UPSERT" to the real ProcedureAudit table in a different database, adding a log date. I could use that table to get the information I want.

Thanks again!

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

FYI, I ended up using CONTEXT_INFO to store the name of last procedure called.

Thanks for the tip

-- Gianluca Sartori