0

I have a table that runs several stored procedures and stores the resulting rows in the target table [TestTable]

[TestTable] is first populated by the stored procedure [spInsertRowToTestTable] and then Updated by [spUpdatesRowInTestTableFirst] and [spUpdatesRowInTestTableLast]

I can capture the responsible procedure of initial insert in the table by using a Default Constatraint to set the @@PROCID. I would like to capture Updates in a similar way, without modifying existing procedures that result in updates to the row.

I have tried, but I have not been able to create a trigger or a similar solution that captures any subsequent Updates. The scope of the calling procedure aludes my atempts to catch it in a trigger and update the row.

Is there a straight forward way to do this?

See example code below:

--DROP SCHEMA test;
--GO
--DROP TABLE test.TestTable;
CREATE SCHEMA test;
GO

CREATE TABLE test.TestTable
    (
      Column1 NVARCHAR(20) NULL ,
      RowInsertedBySP NVARCHAR(30)
        NULL
        CONSTRAINT DF_TestTable_RowInsertedBySP DEFAULT OBJECT_NAME(@@PROCID) ,
      RowUpdatedBY NVARCHAR(20)
    );
GO

CREATE PROCEDURE test.spInsertRowToTestTable
AS
    INSERT  INTO test.TestTable
            ( Column1 )
    VALUES  ( 'TestRow' );
GO

CREATE PROCEDURE test.spUpdatesRowInTestTableFirst
AS
    UPDATE test.TestTable
    SET Column1 = 'Updated ' +[Column1]; 
GO

EXEC test.spInsertRowToTestTable;

SELECT  *
FROM    test.TestTable AS tt;

Resulting in:

Column1              RowInsertedBySP                RowUpdatedBY
-------------------- ------------------------------ --------------------
TestRow              spInsertRowToTestTable         NULL

(1 row(s) affected)

I would simply like to catch the ids of the procedures that do Updates on the rows in column [RowUpdatedBy].

i.e if i have [spUpdatesRowInTestTableFirst], [spUpdatesRowInTestTableLast]. The column [RowUpdatedBy] should then reflect the updates like this:

Column1              RowInsertedBySP                RowUpdatedBY
-------------------- ------------------------------ --------------------
TestRow              spInsertRowToTestTable        spUpdatesRowInTestTableFirst, spUpdatesRowInTestTableLast    

I have seen a example of using DBCC Outputbuffer, but i would rather not go down that road. We use Enterprise edition, so another idea would be to leverage CDC.

I would appreciate any input.

2 Answers 2

1

if you want to know who wrote what and when, I would create 2 users, one for each of the procedures involved, and then create procedures with execute as user

add an extra column on your testtable to keep the session_user or current_user so that you know who inserted/updated each record since insert_procedure will be executed as insert_user and update_procedure will be executed as update_user.

this script can help you to find out the original login, and compares different ways of addressing the users.

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), 
               CHARINDEX('\', 
               SUSER_SNAME()) + 1, 
               LEN(SUSER_SNAME()))


SELECT   @USER 
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        ,USER
        ,SESSION_USER


EXECUTE AS LOGIN='my_user'

--DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), 
               CHARINDEX('\', 
               SUSER_SNAME()) + 1, 
               LEN(SUSER_SNAME()))


SELECT   @USER 
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

REVERT
1
  • 1
    Thanks for your reply, i would however like to find a solution that does not require me to change any of the Updating procedures, much like how i catch the Inserting procedure.
    – Viggos
    Aug 18, 2016 at 14:43
1

I think you can achieve the same result without using trigger. You can simply make a small change to your update proc as shown below

CREATE PROCEDURE test.spUpdatesRowInTestTableFirst
AS
    UPDATE test.TestTable
    SET Column1 = 'Updated ' +[Column1]
    , RowUpdatedBY = isnull(RowUpdatedBY, '') + object_name(@@procid) +',' -- adding this line to record the update
GO

But we need to EXPAND your table's [RowUpdateBy] column from current nvarchar(20) to NVARCHAR(2000) to avoid issue.

3
  • Yes that is of course a option. But this was a simple example, and i am looking for a solution that does not requoire me to update all the proceedures. But rather capture it via a trigger or the table itself.
    – Viggos
    Aug 18, 2016 at 11:49
  • @Viggos, I think your question is similar to this question: stackoverflow.com/questions/14467961/… In short, there is no way for a trigger to know the name of a procedure that fires the trigger, but there is some not-reliable workaround as mentioned in the above link.
    – jyao
    Aug 18, 2016 at 17:04
  • i am starting to settle on that fact also. I will probably end up running a modification on script on all the update statements. It would have been nice to capture it from the other side thou...
    – Viggos
    Aug 19, 2016 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.