I'll start with a very simple example: two tables, both with the same schema, clustered on PK, but one of which has an
INSTEAD OF UPDATE trigger:
CREATE TABLE Standard ( PK UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, V INT NOT NULL ) GO CREATE TABLE InsteadOf ( PK UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, V INT NOT NULL ) GO INSERT Standard (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0) INSERT InsteadOf (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0) GO CREATE TRIGGER TR_InsteadOf_Update ON InsteadOf INSTEAD OF UPDATE AS BEGIN DECLARE @PK UNIQUEIDENTIFIER DECLARE @V INT DECLARE @cursor CURSOR SET @cursor = CURSOR FOR SELECT PK, V FROM Inserted OPEN @cursor FETCH NEXT FROM @cursor INTO @PK, @V WHILE @@FETCH_STATUS = 0 BEGIN UPDATE InsteadOf SET V = @V WHERE PK = @PK FETCH NEXT FROM @cursor INTO @PK, @V END CLOSE @cursor DEALLOCATE @cursor END GO
If I view the query plan for an update against the standard table, I get the expected clusted index update:
UPDATE Standard SET V = 1 WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'
However if I perform a similar update against the table with the trigger, I get what appears to be a clustered index insert, as well as the clustered index update:
UPDATE InsteadOf SET V = 1 WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'
Why is this? I can see the clustered index update I expected later in this query plan (query #4), but why do I get this extra insert at query #1?