How to deal with update of PK in ON INSTEAD OF UPDATE Trigger

Question

I am trying to construct an INSTEAD OF UPDATE trigger that modifies one of the columns being inserted, before doing the insert. Works fine if the PK of the table is not changed during the update.

If the PK of the table is updated, I don't know how to write an update statement to update the appropriate rows. How do I know which rows to update.

CREATE Table MyTest(ID int NOT NULL PRIMARY KEY,
                    Name NVARCHAR(40) NOT NULL);
GO

INSERT INTO MyTest VALUES(1,'Joe');
INSERT INTO MyTest VALUES(2,'Scott');
INSERT INTO MyTest VALUES(3,'Dan');
INSERT INTO MyTest VALUES(4,'Becky');

GO

CREATE TRIGGER Update_Please
ON MyTest
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    IF EXISTS (SELECT id  
                 FROM INSERTED 
               EXCEPT 
               SELECT id
                 FROM DELETED)
        RAISERROR ('now what?',-1,-1);
        /* ======================================================
           oh heck! The PK in one or more rows was updated 
           in the update statement. How do I construct
           an update statement w/o joining to inserted on the PK?
           ====================================================== */

    ELSE
        BEGIN
            -- ALTER A COLUMN
            UPDATE M
               SET name = CASE WHEN I.NAME = 'Mike' THEN 'The Dude' ELSE  I.NAME END
              FROM MyTest M
              JOIN INSERTED I ON I.id = M.id;
        END
END

GO

UPDATE MyTest SET Name = 'Mike' WHERE id > 2; --works great!

UPDATE MyTest SET ID = 9 WHERE Name = 'Joe'; --how do I complete the update?

I don't think that's possible using an `instead of update` trigger, but you could use a `for update` trigger to do it... — Zohar Peled, Oct 02 '18 at 04:30
@ZoharPeled - if you have an example that would really help me out, I'm familliar with `BEFORE UPDATE` and `AFTER UPDATE` triggers, but I don't know what a `for update` trigger is. — mike, Oct 02 '18 at 04:35

score 3 · Accepted Answer · answered Oct 02 '18 at 04:41

As I wrote in my comment, I don't think you can do that in an instead of update trigger, however it's quite easy to do that with a for update. The reason is the difference between the two triggers - the instead of update trigger is fired before the underlying table is updated, while the for update trigger is fired after that. This means that whatever values you have in your inserted table are the same values you have in your actual table - so just change the trigger from instead of update to a for update:

CREATE TRIGGER Update_Please
ON MyTest
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON
        UPDATE M
           SET name = CASE WHEN I.NAME = 'Mike' THEN 'The Dude' ELSE  I.NAME END
          FROM MyTest M
          JOIN INSERTED I ON I.id = M.id;

END

GO

AH! Ok, I read https://stackoverflow.com/questions/5340638 Now I get it, a `for update` is the same as an `AFTER UPDATE` trigger, so the code above responds AFTER the original update. Thank you again for steering me in the right direction. — mike, Oct 02 '18 at 18:58

How to deal with update of PK in ON INSTEAD OF UPDATE Trigger

1 Answers1