This is another post about Triggers, but in this one we’re going to focus more on the Inserted and Deleted tables that a Trigger has access to.
This will explain how they’re created and therefore how Triggers can cause unexpected issues when reading Execution Plans.
There are differences between the two types of Trigger when it comes to how these internal tables work. One being a lot more obvious than the other.
Let’s start with the simplest… the AFTER Trigger.
This one is the easiest to understand as the Trigger fires AFTER the action that you have performed. Therefore at this point we know the before and after record, all we need is access to it. Therefore SQL Server simply uses versioning, just like it would if you were to turn on Snapshot Isolation level.
As such, SQL Server simply utilizes the version store in tempDB in order to fulfil our requirements.
Now, where we have an issue is with the INSTEAD OF Trigger.
What you have to remember here is that we haven’t made a change… by definition we’re not performing the action in question, but replacing it with something else. Therefore we cannot use versioning.
Therefore what we end up with is something like the following:
if object_id('prices') is not null drop table prices
create table prices
priceID int identity,
price decimal(19, 2)
insert into prices
values (1, 100.00),
create trigger tr_priceDelete
instead of delete
from prices p
join deleted d
on p.priceID = d.priceID
delete from prices
Fine so far? But let’s look at the execution plan for the DELETE:
As you can see, this is strange… we’re performing a Delete and yet the Execution Plan is telling us that we’re performing an Insert?
So what’s happening is that SQL Server has to use a WorkTable in order to store the changing records and therefore it is performing an Insert into the WorkTable in order to store a copy of the records.
Just a warning in case you ever see a very strange Execution Plan in which Deletes seem to be performing Inserts.