Dan Abramov Dan Abramov - 7 months ago 20
SQL Question

Equivalent of C# 'readonly' for an MS SQL column?

Imagine there is a

Price
column in
Products
table, and the price may change.

I'm fine with it changing but I want to store the original
Price
value in another column
.

Is there any automatic way MS SQL server may do this?

Can I do this using Default Value field?

Do I have to declare a trigger?

Update



I tried to use
Price
to simplify the question but it looks like this provoked "use separate table" type of answers.

I'm sorry for the confusion I caused.

In the real world, I need to store a foreign key ID and I'm 100% I only need current and original values.

Update 2



I got a little confused by the different approaches suggested so please let me explain the situation again.

Imaginary
Products
table has three fields:
ID
,
Price
and
OriginalPrice
.

I want to set
OriginalPrice
to
Price
value on any insert
.

Sometimes it is a single product that gets created from code. Sometimes there are thousands of products created by a single
insert
from a stored procedure so I want to handle these properly as well.

Once
OriginalPrice
has been set, I never intend to update it.


Hope my question is clearer now.

Thanks for your effort.

Final Update



I want to thank everyone, particularly @gbn, for their help.

Although I posted my own answer, it is largely based on @gbn's answer and his further suggestions. His answer is also more complete, therefore I mark it as correct.

gbn gbn
Answer

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....