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 setOriginalPrice 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 O开发者_C百科riginalPrice 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.

---

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

---

There is no readonly attribute for a SQL Server table column. BUT you could implement the functionality you describe using a trigger (and restricting permissions)

Except, it is not the best way to solve the problem. Instead treat the price as Type 2 'slowly changing dimension'. This involves having a 'ValidTo' column (os 'StartDate' and 'EndDate' columns), and closing off a record:

Supplier_Key    Supplier_Code   Supplier_Name   Supplier_State  Start_Date  End_Date
123           ABC             Acme Supply Co    CA            01-Jan-2000   21-Dec-2004
124           ABC             Acme Supply Co    IL            22-Dec-2004   

If you do go the route of a trigger (I suggest you use SCD type 2), make sure it can handle multiple rows: Multirow Considerations for DML Triggers

---

I would recommend storing your price in a seperate table called Prices, with the columns Price and Date.

Then whenever the price is updated, INSERT a new record into the Prices table. Then when you need to know the current price, you can pull from there.

---

However, if you wish to update an OriginalPrice column automatically, you could add a TRIGGER to the table to do this:

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx

---

This is what I ended up with, with a heavy help from @gbn, @Mitch and @Curt:

create trigger TRG_Products_Price_I --common-ish naming style
    on dbo.Products after insert as
begin
    set nocount on

        update m
        set OriginalPrice = i.Price 
        from Products p
        join inserted i on p.ID = i.ID

end

I tried to follow this article as well.
Thanks to everyone!