I have a request from client to keep all the changes made to a record. This table has 37 fields and user may click on save without making any changes.
I have to copy the original record into another table if there was any changes done to it. User clicks on a SAVE button and I have to know if there was any changes made before copying the record. Is there a quick way to tell if any fields were changed so I can copy the record before saving the changes.
Not tested, but something like (where all the table field structures are the same):
INSERT INTO [LOGTABLE] SELECT * FROM [ORIGINALTABLE] JOIN [COPYTABLE] ON [ORIGINALTABLE].[PK_FIELD] = [COPYTABLE].[PK_FIELD] WHERE [ORIGINALTABLE].[F1] <> [COPYTABLE].[F1] OR [ORIGINALTABLE].[F2] <> [COPYTABLE].[F2] OR [ORIGINALTABLE].[F3] <> [COPYTABLE].[F3] . . .
I don't think your example will work.
There may be several changes to original record stored in copytable. I have to keep the changes made in ascending date order.
User selects a current record then I display it on screen for user to edit. User may change none, one, two or 30 fields then click on save.
I have to compare data on screen to original record. If data in any one of the fields is changed then I copy the original record to copytable and save the changed data to original record.
Comparing what is on screen to original record is where I need help.
Are you using any sort of ORM, or any form of MVC?