Tracking changes manual using timestamps or automatic?

Question

I have two databases synchronized using tracking changes of SqlServer 2008 Tracking Changes and it's cool because without effort I can control the inserts/updates and deletes of a database and send to the other. Currently I'm migrating the system to MySQL. Does exists something similar to track changes or I have to implement it manually.

To implement this manually is a good approach use a timestamp to control INSERTS/UPDATES and triggers to fill a deletions table to control DELETES?

When I say timestamp I want to say SQLServer timestamp (binary field that automatically increments when the register is inserted, modified. Does exists something similar to SQLServer timestamp in MySQL?

Thanks

score 0 · Accepted Answer · answered Jul 27 '12 at 06:25

Yes, you can monitor changes in a table (INSERTs, UPDATEs) using MySQL TIMESTAMP field; the TIMESTAMP field has to be created with ON UPDATE CURRENT_TIMESTAMP option. It will help you to know when the record was changed.

For example -

CREATE TABLE table1(
  id INT(11) NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

More information here - Automatic Initialization and Updating for TIMESTAMP.

Tracking changes manual using timestamps or automatic?

1 Answers1