Whats the best way to audit changes on a particular form field??

Question

I need to add an entry into a database everytime a field changes in a form indicating the old value and the new value.

How would you go about this??

Would you...

1. Add a hidden field for every field and compare it to the new value on submit then add an audit entry if neccessary??

2. Do a select of the data to be inserted on post then compare each property and insert audit entry.

3. Any other ideas?

Cheers.

Answer 1

i think that first you can serialize the form to string , and compare with the latest string , to see if something change . if 2 string equal you have nothing to do.

i supposed that all element in your form named by [] like :

form_name['first_name'] .... form_name['last_name'] ....

if you using php you can do array_diff_assoc , to see what change.

all you need is save every time in db the latest form array , save as string by serialized .

Answer 2

You may be able to use an update trigger in your database to do the comparing and auditing. (I don't have a code sample handy - sorry).

Answer 3

If you use detailsview/FormView, you can get old and new values when you are saving(on inserting/updateing event of DetailsView/Formview) values in the DB.

Answer 4

You din't say what database you are using. You can try writing a procedure, which gets the current value and compare, store in database. I suggest this as I think, this is more of a comparision to be done on database side and not on front end.

Edit: Hey, if you use Oracle it is easy to write procedures. :)

Answer 5

If you're using Postgres or any other database that has functions you can easily write a function to do that for you that could be triggered whenever an UPDATE on a table is performed

For point 1, It's definitely not a good idea to include 'hidden fields' in the form as someone could easily craft their own POST statement.

For point 2, This would work easily enough and is what I'd use if I couldn't write a function.

You'd wanna do something like this:

1. Validate that what they are submitting is valid
2. Select Information from the non-updated row
3. Store both the form data and the db data in two arrays, for instance $db_data and $form_data

4. Get the differences using the function array_diff_assoc

   $differences = array_diff_assoc($db_data,$form_data);

Keeping in mind that both the form and db array need to have the same keys.

Answer 6

Assuming you want to keep this in the app layer I would advice to use a model layer with properties per field to do the logging. All data access then goes trough this data model, giving you a hook to add functionality.

An activerecord based example (VBScript):

   class cSomeEntity
       public db ' link to a db wrapper
       private id, dirty, loaded ' varous flags 

       private sub class_initialize
         dirty = false
         loaded = false
       end sub

       private sub class_terminate
        if dirty then
            db.execute("update some_table set some_field=? where id=?", array(p_some_field, id))
        end if
       end sub

       public sub load_by_id(value)
         dim rs
         set rs = db.fetch_rs("select id, some_field from some_table where id=?", array(id))
         id = rs("id")
         p_some_field = rs("some_field")        
         loaded = true
       end sub

       private p_some_field
       public property get some_field
         some_field = p_some_field
       end property

       public property let some_field(value)
         if not loaded then err.raise 1, , "Entity not yet initialized, call .load_by_id() first!"
         if value <> p_some_field then
           dirty = true
           make_log_entry("some_value", p_some_field, value)           
           p_some_field = value      
         end if
       end property

       private sub make_log_entry(field, old_value, new_value)
         db.execute("insert into audit_log (table, field, old_value, new_value) values (?, ?, ?, ?)", _
           array("some_table", field, old_value, new_value))     
       end sub 
    end class

It might seem a bit bloated, but it is more flexible than a trigger based approach. For example you can easily implement range checks and such.

Secondly, when you need to write multiple entity classes you can push a lot of functionality in to a delegate class and use code template to write the property getter & setters.