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
TopAnswers
Databases
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