Creating separate table to keep track of a specific column using TIMESTAMP data type

Question

I've a mysql table which has two columns changes frequently. One is "available"(mainly 0,1) & the other is "notification_preference". Now, I wanna create a "updated_at" column to keep track of of the changes of "available" column.

I've learnt that TIMESTAMP type updates automatically if a column of the corresponding row changes. I want to use "updated_at" column to keep track only of the "available" column.

So the question is, should I create a new table with "available" & "updated_at(TIMESTAMP)" or keep data in the same table and change the "updated_at" table manually? The main table is the following :

...........................................

id int

name varchar

username varchar

password varchar

available tinyint

updated_at DATETIME

What I'm wanting to do is :

Main Table

...........................................

id int

name varchar

username varchar

password varchar

available tinyint

updated_at DATETIME

Another Table for available

...........................................

user_id

available tinyint

updated_at TIMESTAMP

which procedure is better?

There are other columns in the main table that might change sometimes, so I can't use TIMESTAMP for "updated_at" in the main table. Because if other columns of this table get updated, then "updated_at" will be updated too, but I don't want this. Thanks in advance.

Do you need to keep track of every `updated_at` value, or just the latest one? — Tim Biegeleisen, Dec 09 '17 at 09:52
Edit your question and show us data which demonstrates your problem. It isn't clear what you want to do here. — Tim Biegeleisen, Dec 09 '17 at 10:47
Edit your question and show us data which demonstrates your problem. It isn't clear what you want to do here. — Tim Biegeleisen, Dec 09 '17 at 10:48

score 1 · Accepted Answer · answered Dec 09 '17 at 13:24

You can use a trigger which will update the updated_at column only if the value in available has been changed:

create trigger mainTable_before_update before update on mainTable
for each row begin
    if new.available <> old.available
        then set new.updated_at = now();
    end if;
end

For inserts you can still use DEFAULT CURRENT_TIMESTAMP in the CREATE statement:

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

As you will see in the Demo the column is not updated when we change the password. But it is updated when we change available.

Creating separate table to keep track of a specific column using TIMESTAMP data type

1 Answers1