34

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?

Mat
  • 10,079
  • 4
  • 42
  • 40
Philipp M
  • 443
  • 1
  • 5
  • 10

4 Answers4

31

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

This Connect item requested it but was rejected. Add DRI to enforce immutable column values. See also Allow us to mark columns as immutable or const on the current feedback sit

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Martin Smith
  • 84,644
  • 15
  • 245
  • 333
15

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)

Community
  • 1
jaraics
  • 273
  • 2
  • 6
4

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;
msi77
  • 1,135
  • 1
  • 7
  • 9
-1

I know this is rather old, but there is a way to prevent a column from being UPDATED, which is the reason why SQL does not need an immutable schema, and is the same reason why Microsoft SQL Server did not ever fulfill the request linked by @Martin Smith in the answer.

The following line will prevent your table from being updated or altered anyhow.

GRANT INSERT ON database.immutable TO 'user'@'localhost' IDENTIFIED BY 'password';

You can grant every permission on every database and table, but keep "INSERT" only wherever you need a pseudo-immutable table. Hope this helps.

joaquin
  • 1