April 17, 2002 at 12:27 pm
We need to log the before and after values of fields that have been updated in a table.
Also need to log the userId of the person who made the change. Our web app comes to the database with a single Sql server account 'WebSqlUser'. We have userID's for every user that logs on to the app and we pass these in to stored procs when creating orders and doing other things. Inside the proc we use the @userid to look for certain privileges that the user has and record the order creator.
Now ideally for logging, we would use a trigger on the table that could look for fields that have been updated and put the values from the deleted and inserted tables into our log table; but the problem is that there is no way for us to then log the identity of the person who made the change, we know that triggers are not called by the app and can not take @userid as input.
On the other hand, we could attach the logging logic to the update and insert procs, that would let us have the @userid which would be one of the inputs to these procs, but then here are some problems with using procs for logging:
1. we would either have to store all the 300 fields in the table even if 1 changed as we do not have the deleted and inserted tables available to us as we do in triggers. Alternatively, we would have to begin a tran, select all the fields in the row being updated inside the update proc itself, compare them with the input param values, find the ones that have changed and log those. This probably is not good because we would be blocking people and specially because this database would be hit by 1000 or more concurrent users.
My question is: Has anyone tackled this problem in a way that is efficient. Again what we need to do is log only the field that have changed along with the ID of the user who made the change.
Any recommendations would be welcome.
April 17, 2002 at 12:42 pm
Forgot to add... Logging with stored procs have the added risk that if someone were to go directly to the database and update the table then the change would not be logged.
Also, I know that we could if we used stored procs put the whole row before it's been updated in the EvenLog table and when we need to use the Event log table have querries that do all the compares between the old and new records. This would however become very complicated if a record is changed say a 100 times and has 300 fields, that's why logging only the fields that have changed would be preferrable.
April 17, 2002 at 12:45 pm
In a similar situation we added a column to tables that need logging and insert/update this column with the userid from the sproc. If this column is not updated or inserted, we grabbed user_name(). Not perfect, but worked well for a web app AND caught DBA/developer changes.
We also included a modified column which has the date/time of the change. This often helps with quick troubleshooting. Auditing, however, still required access to the audit table.
Steve Jones
April 17, 2002 at 1:00 pm
We are looking at just one table as the source of the Event Logging table. Also, we already have a field called datemodified in this table.
The Event log table is not going to be used as a trouble shooting table, it's going to be an extremely important table that the customer support folks are going to look at all the time to answer questions and take proper action. So we would have to be totally accurate and would not be able to stick suser_name().
Now adding this extra column in our case still means that we can not use triggers, the preferred way and also if we use the sprocs then we are going to log all the three hundred columns.
Can you think of anyway to use triggers and still get the userid in somehow.
April 17, 2002 at 2:41 pm
I use user_name() as well. Just another reason to use NT authentication. If you've built so that you your app uses stored procs exclusively, I think that adding the logging to each would work, if a little tedious and redundant. If you use ADO recordsets they contain the before and after values, you could code to figure out the changes on the client, send over a bitmap that told you which columns to log. Not fun. I just log the entire row, makes it easy to follow and easy to restore if I need to. I log from deleted, that way the table holds the current and I have everything before that.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply