Can i get a SQL Server catch Updates to reflect the responsible objectid with a database trigger?

Question

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.

Answer 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

Answer 2

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.