Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'

enter image description here

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'

enter image description here

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?

share|improve this question

migrated from stackoverflow.com Jun 27 at 13:56

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 9 down vote accepted

An INSTEAD OF trigger stores a copy of the rows that would be affected in a hidden work table*. This is the Clustered Index Insert you see. The trigger body reads from this work table* and any data changes in the trigger use the 'normal' operator (Clustered Index Update in your example).


*The query processor internally renames the work table when constructing the user-visible form of the execution plan. When writing to it, it is renamed to the target base table, when reading, it is renamed as inserted or deleted more or less as people would expect to see in a trigger.

share|improve this answer
2  
Gotta love having someone who knows provide an answer! –  Max Vernon Jun 27 at 13:58
    
I was about to look, but you likely know the answer to this: would the xml plan detail show that? –  Max Vernon Jun 27 at 13:59
2  
@MaxVernon No, the rename occurs when the internal execution plan is translated to XML show plan. It's not user-visible, (except when using a debugger :). –  Paul White Jun 27 at 14:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.