any way to detect changes in table??

[ddolsooni]

Hi, all!

I'wondering if there is a way to detect changes in Table like a row added, deleted, or updated without trigger...

My app should find any changes in Tables from the last scheduled time, and update the main server database periodically...

What is the best way to do?

Thanks,

Tommy

 

[piti]

i think the only way is to check the date of the table files in the mysql datadir

if a table is updated the tablename.MYD and tablename.MYI files are changed, so their date atribute is changed

hope this helps

 

[ddolsooni]

Thanks, piti!

But, there is no way to know which record got updated, or which row got added from two table files... right?

Tommy

 

[petersJazz]

You would need to add a timestamp column to every table you need to look into. One good thing about a timestamp is that the first timestamp column in a table gets updated automaticaly when the row gets updated.

Example:
alter table xyz add (changeDate timestamp);

update xyz set text='A new text is added here';

select id, changeDate
from xyz
where changeDate > current_timestamp - 0.2;

Im not shure about the syntax current_timestamp - 0.2 but I think it means now minus 0.2 days.

 

[ddolsooni]

Thanks, PetersJazz!

But I'm not clear with "the first timestamp column in a table gets updated automaticaly when the row gets updated." What do you mean by "the first timestamp column"?

And,,, I don't still know whether the row (changeDate > current_timestamp - 0.2) got updated or added, right?

Thanks.

 

[petersJazz]

But I'm not clear with "the first timestamp column in a table gets updated automaticaly when the row gets updated." What do you mean by "the first timestamp column"?

If you have two or more timestamp columns in a table just one of them gets updated automaticaly. And its the first one in the table definition.

Example:

id INT
description CHAR(80)
indate DATE
startdate TIMESTAMP
ordervalue INT
changedate TIMESTAMP

In this table startdate would get updated if you do this for example:

update table
set description = "new text entered"
where id = 4657;