April 12, 2010 at 5:09 am
Hello. is possible to get the store procedure name from trigger when a update operation was performed? I need to know which store procedure launch the trigger on an update operation.
Thank you very much
Regards,
ruben Ruiz
April 12, 2010 at 4:30 pm
As far as I know, there is no way to record HOW a record was updated (sproc, ad-hoc query, etc.). An after update trigger is fired as a result of an update to a table, but it doesn't know what caused the update.
_________________________________
seth delconte
http://sqlkeys.com
April 13, 2010 at 7:55 am
DML triggers only work for INSERT/UPDATE/DELETE operation on tables and DDL triggers work for schema changes on sql objects. So no help from triggers in your case
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 13, 2010 at 8:28 am
Thank you very much for your answers.
April 13, 2010 at 9:14 am
rcruben
Hello. is possible to get the store procedure name from trigger when a update operation was performed?
May I ask:
1. The purpose of gathering this information?
2. Is all updating done by the use of stored procedures, that is updating in NOT performed by ah-hoc / dynamic procedures from some interface programm?
3. Are you authorized to modify the store procedures which update the table in question?
There is a method by which you can accumulate information via the stored procedure itself but it requires modification (a bit of additional code)
April 13, 2010 at 10:01 am
Hello. I have some store procedure and some trigger that update the table. Sometimes an entries of this table is updated and i didn't found which store procedure is updating the value.
April 13, 2010 at 4:43 pm
This answer my question#2.
Hello. I have some store procedure and some trigger that update the table.
These 2 questions you have not answered
1. The purpose of gathering this information?
3. Are you authorized to modify the store procedures which update the table in question?
My questions #3 - is the most important unanswered question .... can you please reply
April 14, 2010 at 12:45 pm
Is it possible to get the stored procedure name from a trigger when an update operation was performed? I need to know which stored procedure invoked the trigger on an update operation
To get back to the question you asked, you may be able to get the answer you want
DBCC INPUTBUFFER gives you the input buffer (or truncated part thereof) that the application program issued. You can execute the command as part of an INSERT statement into a temporary table or table variable and extract the data from there
System function sys.fn_get_sql() and sys.dm_exec_sql_text() are of less use since they return the definition of the trigger you are executing in
I have not as yet found a way to retrieve the SQL text images except for the first and last. Anybody with that answer please speak up now
There are some level-dependent technical issues with the table you insert into and the roles required, so it could be useful to understand what level of SQL you are intending to implement this on
April 14, 2010 at 1:17 pm
There is an outstanding Microsoft Connect (connect.microsoft.com) item requesting that this kind of information be available. Short of coding something that keeps track of its own call stack there isn't something available. You might be able to get some of the SQL using some of the posted methods but nothing is clean..
CEWII
April 15, 2010 at 9:50 am
you can do something like this in your trigger.
Obviously this is risky as if the output of DBCC INPUTBUFFR changes your trigger will break.
DECLARE @ProgramName nvarchar(128)
DECLARE @Text nvarchar(4000)
SELECT @ProgramName = program_name FROM sys.dm_exec_sessions WHERE Session_id = @@SPID
--get the text for what deleted the record
DECLARE @Buffer table ( EventType nvarchar(30), Parms int, EventInfo nvarchar(4000) )
INSERT INTO @Buffer ( EventType, Parms, EventInfo )
EXEC ('DBCC INPUTBUFFER(@@SPID)')
SELECT @Text = EventInfo FROM @Buffer
April 15, 2010 at 9:59 am
Thank you very much. i'm going to test this solution.
Regards,
Ruben
April 16, 2010 at 8:21 am
I have encountered the exact same requirement where I needed to know what stored procedure was performing the data modification.
The technique that I used was to use CONTEXT_INFO as a mechanism for passing a piece of data to the trigger.
BOL:
Using Session Context Information
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
CONTEXT_INFO
http://technet.microsoft.com/en-us/library/ms187768(SQL.90).aspx
CONTECT_INFO() Function
http://technet.microsoft.com/en-us/library/ms180125(SQL.90).aspx
Example:
In the stored procedure:
DECLARE @ContextInfo varbinary(128);
SET @ContextInfo = CAST('usp_FooCopy' AS varbinary(128));
BEGIN;
SET CONTEXT_INFO @ContextInfo ; -- Set the CONTEXT_INFO...
UPDATE MyTable SET
MyColumn =
WHERE ... ;
SET CONTEXT_INFO 0x0; -- Clear the CONTEXT_INFO...
END;
In the trigger:
IF (CAST(ISNULL(CONTEXT_INFO(), 0x0) AS varchar(128)) NOT IN ('usp_FooEdit', 'usp_FooCopy'))
BEGIN;
-- Trigger logic
END;
April 16, 2010 at 8:53 am
tony.turner (4/14/2010)
Is it possible to get the stored procedure name from a trigger when an update operation was performed? I need to know which stored procedure invoked the trigger on an update operation
To get back to the question you asked, you may be able to get the answer you want
DBCC INPUTBUFFER gives you the input buffer (or truncated part thereof) that the application program issued. You can execute the command as part of an INSERT statement into a temporary table or table variable and extract the data from there
System function sys.fn_get_sql() and sys.dm_exec_sql_text() are of less use since they return the definition of the trigger you are executing in
I have not as yet found a way to retrieve the SQL text images except for the first and last. Anybody with that answer please speak up now
There are some level-dependent technical issues with the table you insert into and the roles required, so it could be useful to understand what level of SQL you are intending to implement this on
Been there done that with Inputbuffer. The amount of data for ad hoc queries is very limited (256 characters IIRC). Also I suffered a big perf hit (but I was logging all the ops as well and I had a ton of cursors in the code.).
April 16, 2010 at 10:54 am
Mauve (4/16/2010)
Example:In the stored procedure:
DECLARE @ContextInfo varbinary(128);SET @ContextInfo = CAST('usp_FooCopy' AS varbinary(128));
BEGIN;
SET CONTEXT_INFO @ContextInfo ; -- Set the CONTEXT_INFO...
UPDATE MyTable SET
MyColumn =
WHERE ... ;
SET CONTEXT_INFO 0x0; -- Clear the CONTEXT_INFO...
END;
Something that might be helpful to avoid hardcoding a different name in each stored procedure as above (applies to trigger and function too) is to use something like the following:
--Dynamically get proc, func, trigger name
DECLARE @thisObjName nvarchar(128);
SET @thisObjName = OBJECT_NAME(@@PROCID);
--now use it how you wish/need
DECLARE @ContextInfo varbinary(128);
--cast the proc name that was retrieved dynamically
SET @ContextInfo = CAST(@thisObjName AS varbinary(128));
BEGIN;
SET CONTEXT_INFO @ContextInfo ; -- Set the CONTEXT_INFO...
UPDATE MyTable SET
MyColumn =
WHERE ... ;
SET CONTEXT_INFO 0x0; -- Clear the CONTEXT_INFO...
END;
Or you might just find it usedful to use the @thisObjName in some other manner to "record" that a specific proc, trigger, func did the operation by inserting to some new audit field or external audit table? But either way this requires the additional coding to be implemented in each one that needs to be tracked...
August 14, 2017 at 2:11 am
Joel Ewald - Thursday, April 15, 2010 9:50 AMyou can do something like this in your trigger.Obviously this is risky as if the output of DBCC INPUTBUFFR changes your trigger will break.DECLARE @ProgramName nvarchar(128)DECLARE @Text nvarchar(4000)SELECT @ProgramName = program_name FROM sys.dm_exec_sessions WHERE Session_id = @@SPID--get the text for what deleted the recordDECLARE @Buffer table ( EventType nvarchar(30), Parms int, EventInfo nvarchar(4000) )INSERT INTO @Buffer ( EventType, Parms, EventInfo )EXEC ('DBCC INPUTBUFFER(@@SPID)') SELECT @Text = EventInfo FROM @Buffer
Thank you for your brilliant solution!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply