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.