We are building a new OLTP database that has a design consideration that I am looking for feedback on. that consideration is tracking history of change on every row of every table in the design. CDC has been ruled out because its missing some feature that
My first thought for tracking history is to use triggers to capture changes to an audit table. however, the current design uses the stored procedures that insert/update rows to write to the audit tables. basically the current row is queried for and stored
in a temp table...then a hash is created on the row...a hash is created on the new row and if they are different a row goes into the history table and the row is updated.
(i think this is bad design as the same code would have to be repeated for each procedure written that updates a table, and you have about 20-30 lines of code cluttering up every stored proc. for some reason some developers think triggers are evil though
because they are "hidden")
the second thought on these history tables is that a row should be entered into the history table on insert of a row AND on update...the update should insert the new value. I find that most people only want to store the old value of the changed record
and not the new value. the reason i would store the new value is so you dont have to look in 2 tables to find what a recor
View Complete Post