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....) ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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
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
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!