Can parameterized queries be fully captured using

Question

In SQL Server 2008, I am using triggers to capture all changes made to a specific table in my database. My goal is to capture the entire change. That is, to capture what data is being inserted, not just that data is being inserted. In the trigger I am using the EventInfo column of the result set returned by DBCC INPUTBUFFER to get the currently executing SQL statement as well as the Parameters column to get a count of the parameters used. This works in most cases, but when an external application executes a query using ADO.NET, or rows are inserted/deleted using Edit Top 200 Rows in SSMS, EventInfo doesn't have parameter values.

For example, if a string query is executed in a query window or as a non-parametrized string query in ADO.NET, EventInfo shows:

  INSERT INTO DTS_TABLE ([ID] ,[DTS_ID] ,[DTS] ,[TICS])  VALUES (10, 9, GETDATE(), 91234)  

When inserting the same data using Edit Top 200 Rows or a parametrized query in ADO.NET, EventInfo shows (newlines added for readability):

(@id int,@dtsid int,@dts datetime,@tics int)
INSERT INTO DTS_TABLE ([ID] ,[DTS_ID] ,[DTS] ,[TICS])  
VALUES (@id, @dtsid, @dts, @tics)

Is there anyway to access the parameter values within the context of my trigger?

Answer 1

Why would you care about the parameter values? All you should care about is the old and the new values in the table (old and new in the case of an update - for an insert, only new values).

Since you are using SQL Server 2008, you should look at the new Auditing feature. There is a lot more detail available, and the performance is much improved over using triggers.

Here is a page that describes the information available using the new feature.

Here is a how to page on the new feature.