Use trigger on DELETE to retrieve original SQL command and user who did it

Question

I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012.

What I need is the username that executed it and the command executed, then I can insert them on a log table.

I'm finding many example for creating trigger, but none that shows how to retrieve these info.

Answer 1

For the username, there are a bunch of built-in functions that will return the current user name. They are not all the same, so take your pick - I typically use SUSER_SNAME().

For the command, if you were on a supported and non-antique version (even SQL Server 2014 would do), you could use sys.dm_exec_input_buffer to tell you the last batch sent by the current user (identified by @@SPID), so in the body of your trigger you could have:

INSERT dbo.LoggingTable(username, command<, other cols>)
SELECT SUSER_SNAME(), event_info<, other data>
  FROM sys.dm_exec_input_buffer(@@SPID, NULL);

On SQL Server 2012, it's going to be a lot uglier. You'll need a #temp table to hold the output of dynamic SQL executing DBCC INPUTBUFFER:

DECLARE @sql nvarchar(max) = N'DBCC INPUTBUFFER(@me) WITH NO_INFOMSGS;';

CREATE TABLE #dbcc
(
  EventType  nvarchar(128),
  Parameters int,
  EventInfo  nvarchar(max),
  UserName   nvarchar(128) NOT NULL DEFAULT SUSER_SNAME()
);

INSERT #dbcc(EventType, Parameters, EventInfo)
  EXEC sys.sp_executesql @sql, N'@me int', @@SPID;

INSERT dbo.LoggingTable(username, command<, other cols>)
SELECT UserName, EventInfo<, other data>
  FROM #dbcc;