Regarding SQL Server delete trigger

Question

I want to capture the user's name who deletes a row, when delete action is taken then a stored procedure deletes a row from table. We are providing all required parameters to the stored procedure and also the user's name from the front end who is deleting data.

Basically I want to capture the user's name who is deleting from stored procedure from delete trigger. But it is not possible. We can do one thing that before deleting record we can put the user's name into a temp table and get that name from trigger. But there is one problem that at a time two users can delete two different rows. So what will be the best solution? I don't want use any shadow tables. please discuss. thanks

Answer 1

You are basically asking how to pass the parameter into the trigger?

You can use set CONTEXT_INFO inside the procedure and read this inside the trigger.

DECLARE @name VARBINARY(128) 

SET @name = CAST('Martin' AS VARBINARY(128));


SET CONTEXT_INFO @name /*Set it*/

SELECT CAST(CONTEXT_INFO() AS VARCHAR(128)) /*Read it*/


SET CONTEXT_INFO 0x /*Reset it*/

Answer 2

If you've already got a stored procedure, why bother with triggers? Have the stored procedure log who deleted the row.