Immutable SQL Columns

Question

Is it possible to mark an immutable column in MSSQL?

It seems like this would be a useful DDL function; once a value is specified in a string (a “string” is defined as a specific ratio of values ​​to the primary key), it cannot be changed without deleting the string.

Obviously (like most things) this is more than feasible at the application level, but half the SQL DDL fun is checking your application code.

Answer 1

If the user performing the DML is not the owner of the objects, and not the "db_owner" in the database itself, you can simply grant the privilege to "paste", but not renew the privilege for this table:

Assuming a table with id, col1, col2

 grant insert, select, delete on the_table to the_user; grant update (id, col2) on the_table to the_user; 

With these grants, the_user can insert rows and values ​​for all three columns. It can also update the id column and col2 , but not the col1 column.

db_owner (and possibly the creator / owner of the table) can always update all columns. I do not know if there is a way to revoke this privilege from these roles.

Answer 2

No, there is no such function in SQL Server.

The closest I can think of is an update trigger in a table that checks to see if the values ​​in a particular column are the same for the INSERTED and DELETED logical tables and rejects the updates for the changed rows.

Answer 3

As far as I know, with DDL this is not possible. However, you can implement ADD triggers to fit your requirements. In the BEFORE UPDATE trigger, you can throw an exception or do what you want, rather than update the row.

Answer 4

Perhaps using UPDATE TRIGGER, for example:

 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 an identifier).

I reject the update only if the value of AssetTypeID changes. Thus, the column may be present in the update, and if it sets the old value, then it will pass. (I need it so much)

Answer 5

Another approach is to deny the right to update the table and create a stored procedure (which users have the right to execute) that does not update the immutable field.