View with a read only column

Question

Hi I would like to design a query where a column is not updateable, but the rest of them yes.

How can I do this?

Regards

Answer 1

Well, I think you need to use an INSTEAD OF trigger on the view so that the base table isn't updated directly.

Here is an article explaining them. Article

Another example. Sample code shown below. Article2

Sample (of course, you need to modify this to fit your view.)

CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
    IF UPDATE(lastname)
    BEGIN
        RAISERROR ('cannot change lastname', 16, 1)
        ROLLBACK TRAN
        RETURN
    END
GO

Use the trigger to either:

1) error of they try to update that column

2) just don't update that column

Then you should be set.