October 25, 2022 at 9:45 pm
I have a DML trigger and I need to know where the action is being called from, whether it be a simple T-SQL command or a stored procedure. For example, if I have an insert trigger on Table A and that trigger runs, I want to know what code was executed that caused the trigger to run. Ideally, I would like this to work for both a direct command and stored procedure. I am not able to use Extended Events.
October 25, 2022 at 10:11 pm
(1) Not guaranteed, but you could try looking at sys.dm_exec_input_buffer.event_info (SQL 2016+ only, I think). I would issue this command immediately upon entering the trigger, to have the best chance to see the caller.
SELECT eib.event_info, *
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS eib
WHERE es.session_id = @@SPID
(2) You could try the old DBCC INPUTBUFFER.
(3) You could run a server-side trace, although I guess that one may be out if you can't use even extended events.
(4) If you can and are willing to modify the original code, you could set something to indicate the caller to the proc. You could use session values ("variables") or even a specific byte of CONTEXT_INFO.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply