T-SQL: How to deny update on one column of a table via trigger?

sql, sql-server, sql-server-2005, sql-server-2008-r2, t-sql

Solution

CREATE TRIGGER tg_name_me
ON tbl_name
INSTEAD OF UPDATE
AS
IF EXISTS (
   SELECT *
   FROM INSERTED I
   JOIN DELETED D ON D.PK = I.PK AND ISNULL(D.name,I.name+'.') <> ISNULL(I.name,D.name+'.')
)
RAISERROR('Changes to the name in table tbl_name are NOT allowed', 16,1);
GO

Depending on your application framework for accessing the database, a cheaper way to check for changes is Alexander's answer. Some frameworks will generate SQL update statements that include all columns even if they have not changed, such as

UPDATE TBL
   SET name = 'abc', -- unchanged
       col2 = null, -- changed
       ... etc all columns

The `UPDATE()` function merely checks whether the column is present in the statement, not whether its value has changed. This particular statement will raise an error using `UPDATE()` but won't if tested using the more elaborate trigger as shown above.

Problem

Question: In our SQL-Server 2005 database, we have a table T_Groups. T_Groups has, amongst other things, the fields ID (PK) and Name. Now some idiot in our company used the name as key in a mapping table... Which means now one may not alter a group name, because if one does, the mapping is gone... Now, until this is resolved, I need to add a restriction to T_Groups, so one can't update the group's name. Note that insert should still be possible, and an update that doesn't change the groupname should also be possible. Also note that the user of the application & the developers have both dbo and sysadmin rights, so REVOKE/DENY won't work. How can I do this with a trigger ?

Original source