Auditing Data Modification Statements Using Trigger
Here is an interesting problem: how to audit data modification statements against a table? The goal is to log the SQL executed to modify the table using a trigger. Of course, SELECT statements cannot be logged since only INSERT/UPDATE/DELETE can invoke a trigger. One way to implement this is using a server side trace (which actually can capture the SELECT statements too), however that brings unnecessary overhead.
The following query will get the current SQL statement in SQL Server 2005.
SELECT [text]
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S
WHERE session_id = @@SPID;
However, when executed in a trigger body it simply returns the CREATE TRIGGER statement, not the actual modification SQL statement. A better approach that works is using DBCC INPUTBUFFER. The only tricky part is that the DBCC output cannot be directly saved to a table. The workaround is to use INSERT EXEC to execute DBCC INPUTBUFFER as dynamic SQL and insert the results to a table. Here is the complete solution.
-- SQL log table
CREATE TABLE SQLLog (
language_event NVARCHAR(100),
parameters INT,
event_info NVARCHAR(4000),
event_time DATETIME DEFAULT CURRENT_TIMESTAMP);
-- Sample table to audit actions for
CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol CHAR(1));
-- Sample data
INSERT INTO Foo VALUES (1, 'a');
INSERT INTO Foo VALUES (2, 'b');
INSERT INTO Foo VALUES (3, 'c');
GO
-- Audit trigger
CREATE TRIGGER LogMySQL
ON Foo
AFTER INSERT, UPDATE, DELETE
AS
INSERT INTO SQLLog (language_event, parameters, event_info)
EXEC('DBCC INPUTBUFFER(@@SPID);');
GO
-- Perform some logged actions
GO
INSERT INTO Foo VALUES (4, 'd');
GO
DELETE Foo
WHERE keycol = 1;
GO
UPDATE Foo
SET datacol = 'f'
WHERE keycol = 2;
GO
-- Perform non-logged action
-- SELECT cannot be logged
SELECT datacol
FROM Foo
WHERE keycol = 4;
GO
-- Check what we have in the log
SELECT event_info, event_time
FROM SQLLog;
/*
-- Results
event_info event_time
-------------------------------- -----------------------
INSERT INTO Foo VALUES (4, 'd'); 2008-04-24 22:24:31.153
DELETE Foo WHERE keycol = 1; 2008-04-24 22:24:31.170
UPDATE Foo SET datacol = 'f' WHERE keycol = 2; 2008-04-24 22:24:31.170
*/
It is good to note here that DBCC INPUTBUFFER requires the user executing to be member of the sysadmin fixed server role. One way to handle this is to specify user or login with sufficient privileges in EXEC (by using EXEC AS LOGIN or USER:
INSERT INTO SQLLog (language_event, parameters, event_info)
EXEC('DBCC INPUTBUFFER(@@SPID);') AS LOGIN = 'admin_login';