Actually you can save the output from DBCC INPUTBUFFER to a table. Of
course, since a trigger is invoked only on INSERT/UPDATE/DELETE you cannot
audit SELECT statements (for that you can still use server side trace).
Here is a sample trigger that will save the SQL data modification
statements
against a table.
-- 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(@[EMAIL PROTECTED]
);');
GO
-- Performs 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
*/
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|
