View Single Post
  #3 (permalink)  
Old June 10th, 2008, 07:24 PM
Old Pedant Old Pedant is offline
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Why not do it all in SQL???

GBianchi suggested using SELECT INTO, but of course that doesn't work because you can only use it when the "INTO" table does not yet exist. Not so good for a historical log. <grin/>

If you simply record the Primary Key of the record you are displaying, then part of the process of updating the main table would be to just do
    INSERT INTO myHistoryTable
    SELECT * FROM myMainTable WHERE primaryKey = @pk

The only "trick" here is that the history table has to *NOT* declare any of its fields to be primary keys or foreign keys or IDENTITY, but otherwise they have the same types as the fields in the main table.

And a good "trick", too, is to add one more field to the history table:
     WhenAddedToHistory DATETIME DEFAULT getDate()

So now you automatically have a complete audit trail.

If you use a SP to update the main table, you can just put this INSERT INTO as the first action in the SP. Presto. You are done.
Reply With Quote