CODEWITHSUNDEEP
phpmysqltimestamp
Matt Bryson
Matt Bryson

Reputation: 2944

mySQL `date_updated` column only for specific fields: SELECT and INSERT vs TRIGGER?

I have a bunch of resources that are stored in a MySQL db.

Each have a date_modified field that was set to ON UPDATE CURRENT_TIMESTAMP.

however, there are now columns on those tables that we DON'T want to trigger the date_modified.

From what I can see there are 2 options. (excluding refactoring the tables)

1) Before the update, load the current entity back from the DB, check if the fields have changed, and set the date_modified based on that.

2) Create a TRIGGER for each table that will check the NEW fields against the OLD fields and set the time stamp.

I'm torn between the two approaches.

1 will have a bigger overhead, as it has to perform 2 queries.

2 helps maintain data integrity, but potentially could cause issues later as it masks some of the application logic. If a new field is added at a later date, the dev will have to remember to also update the trigger to to ensure it updates the modified column.

Is there a third approach? Somehow in the UPDATE check if the values have changed and then update the date_modified?

Or any other ways to approach this?

Upvotes: 0

Views: 70

Answers (1)

O. Jones
O. Jones

Reputation: 108796

There's a third choice. Mention the date_modified column in the UPDATE query.

For example,

  UPDATE some_table
     SET some_column = 'new value',
         date_modified = date_modified
   WHERE id = some_id_value

This will change the row in the table while preserving the date_modified value.

Upvotes: 1

Related Questions