Imagine there is a
After your update, let's assume you have only old and new values.
Let's ignore if the same update happens in quick succession because of a client-code bug and that you aren't interested in history (other answers)
You can use a trigger or a stored procedure.
Personally, I'd use a stored proc to provide a basic bit of control. And then no direct UPDATE permissions are needed, which means you have read only unless via your code.
CREATE PROC etc ... UPDATE MyTable SET OldPrice = Price, Price = @NewPrice, UpdatedBy = (variable or default) UpdatedWhen = DEFAULT --you have a DEFAULT right? WHERE PKCol = @SomeID AND --provide some modicum of logic to trap useless updates Price <> @NewPrice;
A trigger would be similar but you need to have a JOIN with the INSERTED and DELETED tables What if someone updates OldPrice directly?
UPDATE T SET OldPrice = D.Price FROM Mytable T JOIN INSERTED I ON T.PKCol = I.PKCol JOIN DELETED D ON T.PKCol = D.PKCol WHERE T.Price <> I.Price;
Now do you see why you got jumped on...?
After question edit, for INSERT only
UPDATE T SET OriginalPrice = I.Price FROM Mytable T JOIN INSERTED I ON T.PKCol = I.PKCol
But if all INSERTs happen via stored procedure I'd set it there though....