asked
Accurately matching inserted to deleted rows in an update trigger
How do you accurately tally the inserted and deleted pseudotables in an update trigger, if the field that has been updated is the primary key (database design flaws notwithstanding)? For instance: If I wanted to prevent someone updating a field in a table, I might be inclined to use an `INSTEAD OF UPDATE` trigger, that joins `inserted` to `deleted` by the table's primary key field, and compares the fixed value on both sides, but if the fixed field is the primary key, the trigger looks as follows: CREATE TABLE TestTable ( rid INT PRIMARY KEY ,foo INT ,bar INT ,baz INT ); GO CREATE TRIGGER trPreventRidUpdate ON TestTable INSTEAD OF UPDATE AS BEGIN IF EXISTS ( SELECT 1 FROM inserted i LEFT JOIN deleted d ON i.rid = d.rid WHERE i.rid IS NULL OR d.rid IS NULL OR i.rid <> d.rid ) BEGIN RAISERROR ('I can''t do that, Dave', 16, 1); ROLLBACK; END ELSE BEGIN UPDATE a SET a.foo = i.foo ,a.bar = i.bar ,a.baz = i.baz FROM TestTable a INNER JOIN inserted i ON i.rid = a.rid; END; END GO A deliberately-crafted piece of nastiness, such as: UPDATE TestTable SET rid = CASE rid WHEN 2 THEN 1 WHEN 1 THEN 2 END WHERE rid IN (1, 2); GO ...would happily swap the values around. I know I can `DENY UPDATE` to `public` on the column itself, but people with the `sysadmins` role could still manage it, and they (we?!) make mistakes too!! I also know that I could just use an IDENTITY above (which would throw `Cannot update identity column 'rid'`), but this is a contrived example! Have I just written a duff trigger, or is it really not possible?
I can fix the contrived example (i.e. preventing updates to a certain column), as follows: [
http://technet.microsoft.com/en-us/library/ms187326.aspx][1] UPDATE() can be used as a function to determine whether a column was updated in a trigger: IF UPDATE(rid) BEGIN RAISERROR ('I can''t do that, Dave', 16, 1); ROLLBACK; END ELSE UPDATE a SET a.foo = i.foo ,a.bar = i.bar ,a.baz = i.baz FROM TestTable a INNER JOIN inserted i ON i.rid = a.rid; This doesn't fix the real issue, though -- matching `inserted` to `deleted` if the primary key value changes. I guess I'd really need a `ROWID()`-type function, but I haven't found one, yet... [1]:
http://technet.microsoft.com/en-us/library/ms187326.aspx
Nice. You should mark this as the answer by clicking on the check box.
Just realised that it really only solves the contrived example (i.e. "preventing updates to a column"), but not the actual question title (i.e. "matching inserted to deleted when primary key has changed"). Still looking for a more-complete answer -- I've updated my answer to reflect this...
Since the primary key was updated, you can't match the rows within the inserted/deleted tables. Preventing updates like the trigger in your answer will to the trick. If you *need* to update the rid column then using a separate primary key column that does not change, such as an identity, is the only "workaround".
14 People are following this question.
