Log changes to database table with trigger

Question

I am looking for a good way to log changes that occur on a particular set of tables in my SQL Server 2005 database. I believe the best way to do this is through a trigger that get's executed on updates and deletes. Is there anyway to grab the actual statement that is being ran? Once I have the statement I can easily log it someplace else (other DB table). However, I have not found an easy way (if possible) to grab the SQL statement that is being ran.

Answer 1

You should be able to accomplish this using the system management views.

An example would be something like this:

SELECT er.session_id,
  er.status,
  er.command,
  DB_NAME(database_id) AS 'DatabaseName',
  user_id,
  st.text
FROM sys.dm_exec_requests AS er
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = @@SPID;

I'm not sure this will be as useful to you as a more data-centric logging mechanism might be, though.

Answer 2

do you really need to log the statement that ran, most people log the changed data (INSERTED and DELETED tables within the trigger).

Answer 3

Use a Log Trigger

There is little reason to capture the actual SQL as there can many different statements that change data in the same way.

Answer 4

Triggers are bad, I'd stay away from triggers.

If you are trying to troubleshoot something, attach Sql Profiler to the database with specific conditions. This will log every query run for your inspection.

Another option is to change to calling program to log its queries. This is a very common practice.

Answer 5

MSSQL has virtual tables named 'Inserted' and 'Deleted', which contain newly-inserted and/or newly-deleted and/or newly-updated data records, which you can access from a trigger ... I use these, to know what data has changed (that's instead of being told what statement changed the data).