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

Question

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 ?

Tetrabranchiate

Not an answer to the question as asked, but I think a better solution would be to create a unique constraint on T_Groups.Name, and then, in the mapping table, you can just add a foreign key. – Ottinger

@hvd: True, that's also missing. Some idiot, as I said. – Tetrabranchiate

I didn't mean also, I meant it as an alternative to the trigger. The foreign key would already block updates to T_Groups.Name if the name is used in the mapping table, or (with ON UPDATE CASCADE) ensures the mapping table is updated as required. Or does that also allow something you wish to disallow? – Ottinger

@hvd: Well, it would theoretically be an alternative, but it's not just one table, so it takes too long, and I might oversee one. Additionally, the name does not belong in there, and I don't want to insert restrictions just to have to remove them later, so... – Tetrabranchiate

Answer 1

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.

Jeaz

How to replace name with an old value instead of generating an error? – Pedi

Answer 2

CREATE TRIGGER dbo.yournametrigger ON T_Groups
FOR UPDATE
AS
BEGIN
  IF UPDATE(name)
  BEGIN
    ROLLBACK
    RAISERROR('Changes column name not allowed', 16, 1);
  END
  ELSE
  BEGIN
  --possible update that doesn't change the groupname
  END
END

Conventionalize

How to replace name with an old value instead of generating an error? – Pedi

Answer 3

This is an example of preserving some original values with an update trigger. It works by setting the values for orig_author and orig_date to the values from the deleted pseudotable each time. It still performs the work and uses cycles.

CREATE TRIGGER [dbo].[tru_my_table]  ON [dbo].[be_my_table]
AFTER UPDATE
AS
    UPDATE [dbo].[be_my_table]
    SET
    orig_author = deleted.orig_author
    orig_date   = deleted.orig_date,
    last_mod_by = SUSER_SNAME(),
    last_mod_dt = getdate()
    from deleted
    WHERE deleted.my_table_id IN  (SELECT DISTINCT my_table_id FROM Inserted)

ALTER TABLE [dbo].[be_my_table] ENABLE TRIGGER [tru_my_table]
GO

Ayer

Answer 4

This example will lock any updates on SABENTIS_LOCATION.fk_sabentis_location through a trigger, and will output a detailed message indicating what objects are affected

ALTER TRIGGER dbo.SABENTIS_LOCATION_update_fk_sabentis_location ON SABENTIS_LOCATION
FOR UPDATE
AS
DECLARE @affected nvarchar(max)
SELECT @affected=STRING_AGG(convert(nvarchar(50), a.id), ', ')
  FROM inserted a
  JOIN deleted b ON a.id = b.id
  WHERE a.fk_sabentis_location != b.fk_sabentis_location
IF @affected != ''
BEGIN
  ROLLBACK TRAN
  DECLARE @message nvarchar(max) = CONCAT('Update values on column fk_sabentis_location locked by custom trigger. Could not update entities: ', @affected);
  RAISERROR(@message, 16, 1)
END

Some examples seem to be using:

IF UPDATE(name)

But this seems to evaluate to TRUE if the field is part of the update statement, even if the value itself has NOT CHANGED leading to false positives.

Termagant