Highlighting changes to individual fields

Using mySQL I know that I can include a field ‘Last Updated’ that will record the date that a record has been updated.

But is it possible to identify that a particular field has been updated?

Hi John,

Yes, there are ways that you can identify whether or not a particular field has been updated.

Option one:
you can add a timestamp column (update_TS) to automatically update the timestamp once the data in that column has been updated. Then use custom function or trigger to store the old timestamp under a new column. Use any comparison that you can/want to compare the date and time under the update_TS column to today’s date. You have two columns to compare to find when the field is updated: update_TS and last_update_TS. Use today’s date to determine if the field has been updated today and compared it to when it was last updated.

Option two:
It’s pretty much the same option one, except that rather than doing it by date you do it by user logged. That would work only if users are required to be authenticated before updating a record, otherwise that would not fit your need.

Best of luck, mate!

SG

Thanks Sam - so do I need a timestamp column for each column I want to be able to do this for?

So if I have columns:

Lodge_Name
Lodge_Summary
Lodge_Details

I would need:

Lodge_Name_TS
Lodge_Summary_TS
Lodge_Details_TS

I have tried adding a Lodge_Name_TS field, with a default of NULL (as I have general LastUpdated field for the record with the default of current timestamp). How do I get it to update to the current date if the Lodge_Name field is changed?

John,

Here is a post from stackoverflow.com that might give you more details.

http://stackoverflow.com/questions/8480747/mysql-after-update-trigger-with-field-names

Please let me know if you need more help.

I think that is a bad approach.

I would create a one to many relationship between your main table and a new “field_change_log” table.

The new tale would have the fields…

- id
- main_table_id
- field_name
- field_updated_on

That way you don’t double the size of your existing table and leave it filled with NULLS. (Very inflexible and a horrible use of space IMHO!!)

Do you understand what I am proposing?

Thanks Mikey - that sounds like a more efficient way of handling it.

How would it need to be set up to add required records when changes to the original table are made?

Rephrase your question please

I understand the logic behind having a separate table to log the changes.

But how do records get added automatically to the table logging the changes when the original table gets updated?

Presumably it has to do with triggers, but they are new to me.

You could do it at the application level or at the database level.

In the first case, when your code goes to update a field, it would need to do two queries: UPDATE the main table and INSERT into the log table.

In the second case, you could use a trigger to do the same, although I think that is a lot more complicated.

Does that help?

Thanks Mikey - I think I am mainly unsure about how to INSERT the name of the field that is updated, as that is not a value stored in the table, or (thinking about it) how to differentiate between the new and old values of the field which is changing.

I’ll do some reading up on triggers though, as that looks like the way forward.

If you did this at the Application-level, then it might go like this…

If a Member Profile consists of a Name, Email, Location, and Age and the user changed their “Location” in the “Change Member Details” page, then your code (e.g. PHP) would see that “Location” changed.

So when your code got to the point of updating the database, instead of simply doing an UPDATE, your code would first need to take the field that changed along with a timestamp and do an INSERT into your new Log Table.

Follow me?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.