I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.
I need to capture the records affected before they are affected first. Then execute the command to complete it. The profiler would give me everything that is going on. But will it give me the records affected?
I need to capture the records affected before they are affected first. Then execute the command to complete it.
Triggers operate after the command is executed, so if your requirements are strict about this (don't know why...) then triggers will not help you.
All your commands SHOULD come through stored procedures. You can easily put some code at the top of each procedure to log who ran it, when, and what parameters were supplied.
If it's not practically useful, then it's practically useless.