How To Audit DML Table Changes w/o Triggers?

Whenever any DML activity occurs in a database I need to audit the following:

1. The table that was changed (INS, UPD, DEL)
2. The data value of the primary key of the changed row

For example, if someone executes:

UPDATE payroll SET Salary = 50000 WHERE empid = 123

I need: "payroll" and "123"

For various business reasons I can't use triggers. It is just one of those things...

I've looked at various options on SQL Profiler and while it looks like I can tell there was an action on the "payroll" table it doesn't look likely that I'll be able to figure out that it was on primary key 123. This seems to especially be the case on the execution of a stored proc where data values are passed as @.parameters.

I know there are third-party products that analyze transaction logs with a GUI and let you export these results to a CSV file or Excel. The concern here is that I need something which is an on-going process, so the GUI and human interaction necessary to generate the file doesn't quite cut it. If I'm wrong here and there's a suggestion I'd certainly look at it.

Thanks so much!

Doug

The options are pretty much what you described. Or you could do this in your code by logging the parameters to the SP that does the modification for example. Allowing ad-hoc insert/update/delete to tables directly is not a good thing to do. In SQL Server 2005, you can use event notifications to do this easily.|||Thanks for the feedback...but I'm not sure I understand you ... I cannot use a trigger ... and the log reading tools don't seem to offer a constant flow. So the items I mention in my post won't work.

Is there any other approach?

Thanks again...I appreciate any suggestions.

Doug|||My point was that your options are limited in SQL Server 2000. I am not aware of the fulll capabilities of various 3rd party tools that work with log files directly. Did you check web site of companies like Lumigent? Another idea I was thinking about was to use replication. You could configure log reader agent to output verbose information which will include commands that are being replicated. Maybe this will help. It is hard to tell. And it almost seems like you have to modify your application if existing tools or methodologies do not meet your requirements.|||OK, this gives me a couple of leads to follow. Thank you very much for thinking this over with me, I appreciate your feedback.

All the best!

Doug