Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Johnph
Posting Yak Master
103 Posts |
 Posted - 2014-05-14 : 12:04:08
|
| Hello I have a tables that looks like this:TABLE1COL1COL2LOG_TABLETABLE_NAMEUSER_IDSQL_STATEMENTDATETIMEI need a trigger (I assume) to log any updates/inserts/truncate/deletes/etc. to any changes as well as their USER_ID that has been executed to TABLE1.Is this possible? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-14 : 15:42:53
|
| Sql server 2008 introduced CDC (change data capture). I don't have any practical experience with that yet I can't be much help there.But a very common way to log all data changes is through one or more triggers. That will allow you to capture before and after values, the user context, and the datetime of the event. It only covers (insert/update/delete) events. Truncate is not captured by triggers. However, only admins should be allowed to do that anyway so you should have a security plan in place that doesn't allow anyone but DBAs to connect to the server as admin.It is not common to capture the sql statement as part of a data audit. it is usually sufficient to know which of the three events caused the change. I guess it is possible to get the statement and/or the execution plan but do you really need/want that? I see you have [Table_name] as a column in your log table. It is not a good practice to use a single table to log changes from multiple tables. It is too contentious and will slow down the user transactions. It is a better model to have one audit table per subject table. Would you consider that? For what purpose is this [Log_table]? There may be better options for you.Be One with the OptimizerTG |
 |
|
|
|
|
|
Logging User ID and SQL Statements