Sql prevent row being modified

Question

Is it possible to prevent any changes to a row in sql?

Let's say the record is created. But once it's created I don't want the record to be modified ever.

So the user can still try to do a update table set column = value .. but that would raise an error preventing it to be modified something like that.

Answer 1

you should put a trigger on the table.

the trigger can either replace any new values with the old values, silently refusing the update, or it can throw an exception to the user saying update is not allowed.

OR

you can assign INSERT privilege to the users, but not UPDATE privilege.

Answer 2

yes you could do it with a trigger provided that the user doesn't do something like this

alter table disable trigger
update table...

you can also deny update on that column like this (SQL Server 2005 and up syntax)

DENY UPDATE ON OBJECT::TableName(ColumnName) TO UserNAme;

Answer 3

The easiest way would be to REVOKE UPDATE ON TABLE, if SQL server allows for such fine grained access control.

Note that a malicious user could still DELETE, then INSERT another "changed" record.