Is it possible to get execution call stack in a trigger?

Question

I have 10 stored procedures and each of them does INSERTs into one tableX.

Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ?

Thank you.

Answer 1

Yes, it is possible to identify the running code, by using the @@procid system function, and better OBJECT_NAME(@@PROCID) to have the complete name.

Definition: "Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider."

You can read about it here.

Another option would be to check the sql plan of the current spid and save that info in a logging table. A sample query to be used in each procedure to save audit data would be :

select sp.hostname, sp.program_name, sp.loginame,
    st.text as query_text
from sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st  
where sp.spid = @@spid

Maybe there are too many details there..but I believe that you get the idea.

A third option would be to use the context_info information to the current SP's session. And associate somewhere the context information saved there with each procedure. For example in procedure1 you write 111 to the context, in procedure2 you write 222.. and so on.

A lot more info regarding context_info you can read in this SO question.

Answer 2

XEvents provide another way for getting known a T-SQL stack although SQL Server 2008 mightn't support a used event type. The solution consists of a trigger, an error and an XEvent session. I took Jim Brown's example to show the way it works.

First of all, I tested the solution for SQL Server 2016 SP2CU2 Dev Edition. SQL Server 2008 supports some EXevent, but I don't have any instance so that I couldn't test it.

The idea is to generate a user error in a dummy try-catch block, then catch the error inside an XEvent session with tsql_stack action. SQLSERVER.error_reported XEvent type can catch all errors even though a try-catch block traps them. In the end, sys.dm_exec_sql_text extract T-SQL queries from the query handles which tsql_stack action gives.

An example from Jim Brown's answer, which I developed, is shown below. A trigger raises the error with the text 'catch me'. The XEvent session catches errors only with the text like 'catch me'.

CREATE TABLE  Test ( TestID INT )

GO

CREATE TRIGGER TestTrigger ON Test
FOR INSERT
AS
BEGIN TRY
    SET XACT_ABORT OFF; -- REALLY IMPORTANT!
    /* make an catching a great deal more interesting */
    DECLARE @TestID NVARCHAR(MAX) ;
    SELECT TOP (1) @TestID = CAST(ins.TestID AS NVARCHAR(MAX)) FROM inserted AS ins ;
    RAISERROR (N'catch_me TestID = "%s"' , 11 , 0 , @TestID) ;
END TRY BEGIN CATCH /* NOTHING TO DO */ END CATCH

GO

CREATE PROCEDURE usp_ProcIDTest
AS
INSERT INTO Test ( TestID ) VALUES ( 1 ) 

GO

CREATE PROCEDURE usp_RootProcIDTest
AS
EXEC usp_ProcIDTest

GO

-- This XEvent session definition was kindly provided by XEvent 'New Session' wizard.
CREATE EVENT SESSION [catch_insertion_into_Test] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username,sqlserver.context_info,sqlserver.plan_handle)
    WHERE ([message] like N'catch_me%'))
ADD TARGET package0.ring_buffer(SET max_memory=(10240))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

Now, if you launch XEvent session (SSMS, Object Explorer, Management, Extended Events, Sessions, catch_insertion_into_Test), execute usp_RootProcIDTest and look the XEvent session's ring buffer, you should see the XML which consists the node <action name="tsql_stack" package="sqlserver">. There is a sequence of frame nodes. Put the values of a handle's attribute into the system function 'sys.dm_exec_sql_text', and voilà:

-- REPLACE MY HANDLES WITH YOURS
SELECT * FROM sys.dm_exec_sql_text(0x03000800D153096910272C01A6AA000000000000000000000000000000000000000000000000000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030008000A78FD6912272C01A6AA000001000000000000000000000000000000000000000000000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03000800439CF16A13272C01A6AA000001000000000000000000000000000000000000000000000000000000);

XEvent let you do much more than this! Don't miss opportunities to learn them!

Answer 3

I wanted to do this too. Thanks for the answer. As I'm still here, I'll post my test to save others time :)

CREATE TABLE  Test ( TestID INT )
GO

CREATE TRIGGER TestTrigger ON Test
FOR INSERT
AS

SELECT CAST(CONTEXT_INFO() AS NVARCHAR(128));
GO

CREATE PROCEDURE usp_ProcIDTest
AS

DECLARE @ProcedureName VARBINARY(MAX) = CAST(OBJECT_NAME(@@PROCID) AS VARBINARY(MAX))
SET CONTEXT_INFO @ProcedureName

INSERT INTO Test ( TestID ) VALUES ( 1 ) 

GO

EXEC usp_ProcIDTest
GO

DROP TABLE Test
GO