How to get store procedure name from trigger

  • rcruben

    Mr or Mrs. 500

    Points: 513

    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

  • seth delconte

    SSCertifiable

    Points: 6388

    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

  • Bhuvnesh

    SSC Guru

    Points: 59344

    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;-)

  • rcruben

    Mr or Mrs. 500

    Points: 513

    Thank you very much for your answers.

  • Ron McCullough

    SSC Guru

    Points: 63877

    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)

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • rcruben

    Mr or Mrs. 500

    Points: 513

    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.

  • Ron McCullough

    SSC Guru

    Points: 63877

    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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • tony.turner

    Right there with Babe

    Points: 783

    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

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    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

  • Joel Ewald

    SSCertifiable

    Points: 6009

    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

  • rcruben

    Mr or Mrs. 500

    Points: 513

    Thank you very much. i'm going to test this solution.

    Regards,

    Ruben

  • Mauve

    SSChampion

    Points: 11316

    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;


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.).

  • Adam Gojdas

    SSCommitted

    Points: 1766

    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...

  • optofizik

    SSC Rookie

    Points: 26

    Joel Ewald - Thursday, April 15, 2010 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 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 15 (of 15 total)

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