How to create a column that can never be updated?

Question

In SQL Server 2008, is it possible to create a column that can have a value inserted into it, but can never be updated? In other words, the column can have an initial value inserted into it, but once it contains a non-null value, it can never be changed. If possible, I would prefer to do it without using a trigger.

Thanks - Randy

Answer 1

You can't define a column as Read Only but can you not achieve your goal by setting permission against the column so it can be inserted but not updated by all the relevant users/roles/groups in your database?

Edit:

I misread a bit of your spec, if you need to allow someone to "insert" a null and it only goes read only when a null value is entered then you probably would need a trigger - either to block the updates or to set the deny permissions for the column after a non null value is entered.

Answer 2

You'll have to use a trigger.

You can GRANT INSERT and DENY UPDATE on the column itself, but that would stop you from being able to UPDATE from NULL to something after the initial INSERT.