sql-server sql-server-2014 add tag
lock (imported from SE)
I have a partitioned view that has the below insert trigger (poor mans partition).
When I perform a DELETE, I get the below query plan:

    delete from factproductprice where pricedate = '20170725'

[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/WMiMZ.png

Trigger on the view:

    ALTER TRIGGER [dbo].[factProductPriceDelete] ON [dbo].[FactProductPrice]
    INSTEAD OF DELETE AS
    BEGIN
      IF @@ROWCOUNT = 0 RETURN;
    
      DECLARE @PriceDate DATE
      SELECT @PriceDate = CAST(PriceDate AS DATE) FROM DELETED
      IF @PriceDate BETWEEN '20140101' AND '20141231'
      BEGIN
        DELETE FROM dbo.FactProductPrice2014
    	WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED) 
      END
      IF @PriceDate BETWEEN '20150101' AND '20151231'
      BEGIN
        DELETE FROM dbo.FactProductPrice2015
    	WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED) 
      END
      IF @PriceDate BETWEEN '20160101' AND '20161231'
.... etc
Top Answer
Paul White (imported from SE)
`INSTEAD OF` triggers completely replace the triggering action.

The *inserted* and *deleted* pseudo-tables represent changes that would have been made, had the triggering statement actually executed. Row-versioning cannot be used for these triggers because no modifications have yet occurred, by definition.

SQL Server modifies the execution plan for the triggering DML statement when an `INSTEAD OF` trigger exists. Rather than modifying the affected tables directly, the execution plan writes information about the changes to a hidden worktable.

This worktable contains all the data needed to perform the original changes, the type of modification to perform on each row (delete or insert), as well as any information needed in the trigger for an `OUTPUT` clause.

The Insert in your execution plan represents writing to this hidden worktable. When you capture a post-execution plan for the statement, you will see this hidden worktable being used as the *deleted* and *inserted* pseudo-tables.

See my SQLPerformance.com article, [Interesting Things About INSTEAD OF Triggers][1].


  [1]: https://sqlperformance.com/2015/09/sql-plan/instead-of-triggers

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.