1

I know there are ways to make an audit table in order to get the change history for an entire table in SQL, for ex:

Is there a MySQL option/feature to track history of changes to records?

However, I want to know if there is a way to get the change history for a specific row - ie. a record of edits for row 1 in a table. If there is a way to do this, I would greatly appreciate it. Thanks!

oreos
  • 21
  • 1
  • 1
    Look into writing `UPDATE` and `DELETE` triggers to capture the row being updated and dumping it into an archive or history table. – sniperd Jul 24 '18 at 17:41

3 Answers3

1

What we have done in the past is have change history tables. The first one would be: Change History

  • ch_ID Primary Key
  • Table_Name Name of the table for the change
  • Table_PK The PK from that table
  • Type insert, update, delete
  • Change_Date Date of the change
  • Change_By Who made the change

Second would be: Change History Details

  • chd_id PK
  • ch_ID
  • Column Name
  • Old Value
  • New Value

We then used triggers on the table. You can't use stored procedure to capture the info because DBAs usually don't use stored procedures when making data changes. You can then query by table name and the primary key of the record you are interested in. You can also add screen name to the first table to enable you to get all of the changes made for a record on that screen.

Susan
  • 198
  • 7
  • This seems like it would have a lot of overhead, storing the table name and column names for each change within each change, and old value and new value would have to be something general purpose like VARCHAR. It also seems like it could be cumbersome to reconstruct the state of a row from a particular moment. – Uueerdo Jul 24 '18 at 18:50
  • We have had to do that multiple times and it isn't bad. Depending on how stringent you are with data access, it is also very good at CYA since you can prove that the user made this change on this date. We've also used it to track down errors. I work with very tightly controlled data though so it is worth the overhead. – Susan Jul 24 '18 at 20:58
  • Yes, sadly, it does seem like half the time this even needs done is to show the users that they did it, which of them did it, and when. ...and yeah, on further thought, I suppose the overhead is somewhat negligible considering this kind of thing is usually only done on small tables that are updated a lot and large tables that are updated almost never. – Uueerdo Jul 24 '18 at 21:10
1

I usually suggest having a second history_x table for table x. history_x in this scenario is nearly identical to x; it differs in that it's copy of x's primary key is not primary (and not auto-incrementing even if 'x''s is), and it has it's own primary key and sometimes some sort of addition changed_when datetime field.

Then two triggers are made:

  • AFTER INSERT ON x basically just clones a new row in x to history_x
  • AFTER UPDATE ON x just clones the new state of row x to history_x

How to handle DELETE varies. Often, if you're going as far as to actually delete the x record, the corresponding history records can be deleted with it. If you're just flagging the x as "retired", that is covered by the UPDATE handling. If you need to preserve the history after a delete, you can just add a x_deleted "flag" field and a DELETE trigger that clones the last state of the row, but sets the x_deleted flag in history to "true".

Also, this obviously doesn't track PK changes to x, but could if history_x has two copies of x's PK; one would be the historical PK value captured by the triggers with the rest of the fields, and the second would be bound to a foreign key that would cascade all the old history to reference the new key.

Edit: If you can take advantage of the semi-global nature of session/@ variables, you can even add information such as who made the change; but often connection pooling can interfere with that (each connection is it's own session).

Edit#2/Warning: If you're storing large data such as BLOBs or large TEXT fields, they should probably NOT be cloned every update.

Oh yeah, the "changed_when" data can also be more useful if expressed as a valid_from and valid_until pair of fields. valid_until should be null for the newest history record, and when a new history record in added the previous newest should have it's valid_until field set. changed_when is enough for a log, but if you need to actually use the old values WHERE ? >= valid_from and ? < valid_until is a lot easier than WHERE valid_until < ? ORDER BY valid_until DESC LIMIT 1

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
-1

Based on how it sounds to me, what you want to do is to use RowNumber() in a query.

See here for more details.

Davide Cannizzo
  • 2,826
  • 1
  • 29
  • 31
Ktulue
  • 11
  • 2