9

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

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

11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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