Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm curious if it is possible to create a table with a column that can never be changed, but the other columns of the table can.

For instance I could imagine a CreatedByUser column that should never be changed.

Is there a built-in functionality in SQL Server for this, or is it only possible via triggers or something else?

share|improve this question
    
I do not believe there is any way around this other than implementing triggers and only allowing create/update/delete statements through procedures. –  Mark S. Rasmussen Mar 7 '13 at 11:37
1  
Rejected connect item request –  Martin Smith Mar 7 '13 at 11:45
    
@MartinSmith thanks, for the link. I guess that would be the answer to my question. So make it an answer and I'll accept it. –  Philipp M Mar 7 '13 at 12:07
    
Can you not just revoke update permissions on the column in question? –  Paul White Mar 10 '13 at 4:03

4 Answers 4

up vote 9 down vote accepted

There is no built in declarative support for non updatable columns (except for specific pre defined cases such as IDENTITY)

This Connect item requested it but was rejected. Add DRI to enforce immutable column values

An UPDATE trigger would probably be the most robust way of achieving this. It could check IF UPDATE(CreatedByUser) and raise an error and rollback the transaction if true.

share|improve this answer

You could use a view with derived column. Try this

create table ro_test(id int primary key, CreatedByUser int)
go
create view v_ro_test
as
select id, CreatedByUser*1 CreatedByUser from ro_test
go

insert into ro_test values(1,10);
update ro_test
set CreatedByUser =11
where id =1;
select * from v_ro_test;
go
--ERROR--
update v_ro_test
set CreatedByUser =10
where id =1;

--BUT--
update v_ro_test
set id =2
where id =1;
select * from v_ro_test;
share|improve this answer
    
I'm not sure what you mean. Can you elaborate? –  Philipp M Mar 8 '13 at 14:55
    
@PhilippM Defining the column as an expression in the view prevents it from being updated. –  Paul White Mar 10 '13 at 4:03
    
But you can just update the table and change it's values –  Philipp M Mar 10 '13 at 10:06
    
@Philipp M But you can revoke access to the table and grant it to the view. Does not it? –  msi77 Mar 10 '13 at 20:48

I've made my implementation of the UPDATE TRIGGER approach suggested by Martin Smith's answer as follows:

CREATE TRIGGER trgAfterUpdateAsset ON dbo.Asset
FOR UPDATE AS
IF UPDATE(AssetTypeID) AND EXISTS (SELECT * FROM inserted i JOIN deleted d ON i.ID = d.ID WHERE i.AssetTypeID <> d.AssetTypeID)
BEGIN 
    RAISERROR ('AssetTypeID cannot change.', 16, 1);
    ROLLBACK TRAN
END     

(Note: The table has a Primary Key column, called ID).

I'm only rejecting the update if the value of AssetTypeID changes. So the column could be present in an update, and if the value did not change, than it would pass through. (I needed this way)

share|improve this answer

Why are you updating the createdby column?

I would have two columns a [created_by] and a [modified_by] column, where the first insert would insert all the respective columns in the record, and any subsequent updates would just update the [modified_by] column ( via a trigger on in the application layer you can structure your update to only change the [modified_by] , along with your respective columns )

share|improve this answer
3  
I think you've missed the point of the question. It was asking whether there was any built in support to enforce precisely that the column should not be updatable. –  Martin Smith Mar 7 '13 at 15:21
    
@MartinSmith Exactly - we do have a modified column as well and I only was curious about whether an built in support for readonly exists –  Philipp M Mar 7 '13 at 16:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.