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!