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

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).

Answer 4

Don't forget that your logging will be part of the transaction so if there is an error and you rollback the transaction, your log will also be deleted.

Answer 5

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 6

Triggers are a good way to ensure that any changes are logged, since they will almost always fire regardless of how the updates are performed - e.g. ad-hoc connections as well as application connections.

As suggested by @mwigdahl, the system management views look like a good way to capture the current running batch. Whether that's particularly useful to log in the trigger is another thing.

One downside to using triggers is that you can only identify the source of the update from the database connection. Many applications don't have any user information associated with the connection, to facilitate connection pooling, so you don't know which user is performing the action. ie the Login used by the connection is a generic application login rather than the person using the application. The normal way to get around this is to use stored procedures as the interface to all database interaction, and then ensure that a UserId is passed with all procedure calls. You can then perform your logging via the stored procedure instead of a trigger. Clearly this is only useful if you know people won't update tables directly without using the procedures, or don't need to log that situation.

The ability to get the currently executing batch might provide an even better mechanism: if you ensure that all your sql batches contain a UserId you could then extract this from the sql within your trigger. That would allow you to do all logging with triggers, which means you capture everything, but also let you associate changes with a particular user.

If you're going down the trigger route it's worth checking the situations triggers aren't fired (maybe bulk loaded data? or if people have permission to disable triggers).

Also consider as @idstam pointed out that trigger code will be within your transaction so will normally be logged and rolled back along with it.

Another thing to consider when writing triggers is the behaviour of @@IDENTITY: if you have procedures using @@IDENTITY you might accidentally change their behaviour.

Answer 7

Be careful here, since triggers fire at the ROW level, not the SQL STATEMENT level. So, if someone does "DELETE FROM BIGTABLE", your trigger will fire for each row in that table (this specifically in regard to the fact that you want to know the SQL statement that performed the operation, so you'll need to "figure that out" for every row the statement affects).