CODEWITHSUNDEEP
sql-servert-sqltriggers
mike
mike

Reputation: 2308

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

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?

Upvotes: 1

Views: 265

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 3

Related Questions