This question will probably be elementary to most DBAs.
I'm doing light DB work which isn't my specialty and I'd like to create some kind of "change log" for a table that is copy/appended to a history table each morning. The table is being created in the fashion below, where the entire contents of one table is being appended to a history table with a column on the end called backup_date.
I wrote some SQL to catch new records and deleted records from the previous day but what is the best method to determine what records have changed?
For example: on March 9th, Mr. Gates told HR that he prefers to go by "Bill" instead of "William". Data for March 10th shows that HR made a change to his first name. What is the best method of attack to compare to previous data in the table? I'm mostly interested in comparing to the previous day (each day) but what if I wanted a list of all changes to a particular record?