I need to do a daily export of all of the new rows or updated rows from a legacy database into the new database.
The problem I am running into is that not all of the tables on the legacy database have last modified date columns and we don't want to break anything by adding new columns to the legacy tables.
The worry is that since there are a lot of different processes inserting records, that the columns might not have been specified in those processes, and since we are switching to the new schema it's not worth it to rewrite those processes yet.
See this code contributed by Evan Carroll as to the problem caused by adding another column.
CREATE TABLE f (a int); INSERT INTO f VALUES (1); ALTER TABLE f ADD b int; INSERT INTO f VALUES (2);
This causes the error,
Error Code: 1136. Column count doesn't match value count at row 1
My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.
Then I can export just the new/updated rows and import them into the new database.
Would there be better ways of doing this?