1

I have a db schema where user data is stored using foreign key references , these foreign keys are admin defined . Also , there is some data which is stored without primary keys , however I have other constraints in place to avoid redundancy and other issues .

Due to the requirements of the application when a user 'updates' their info I have to delete all user records from the 'updated' table and reinsert all the user's records again . (I have looked into all other options)

Now because of my search solution (solr) , I need to track changes to the user data (updates/deletes) . I am planning on having a view to compare the last committed data to the real time data . I am fearful of how sustainable it would be to have a stored procedure running every 20 minutes or so , is there a better way of tracking data with SQL ?

Varun Jain
  • 1,901
  • 7
  • 33
  • 66

1 Answers1

2

You could create a change table that contains the same columns as the original table plus another column called "UpdatedOn." Then set up a trigger to write to this change table the original values when the original table is changed.

Example: Original Table:

Name | Address | City
Jane Doe | 1 Main St. | New York

Change to Original Table:

Name | Address | City
Jane Doe | 2 Smith St. | Dubuque

...which triggers an insert to the Change Table:

Name | Address | City | UpdatedOn
Jane Doe | 1 Main St. | New York | 2012-01-01

There is information about using triggers in mysql here: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Valerie Parham-Thompson
  • 1,516
  • 1
  • 11
  • 21