How Inserted and Deleted Tables Work
Published: 2nd September 2015
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:

use testDB
go

if object_id('prices') is not null drop table prices
go

create table prices
(
  
priceID int identity,
  
orderNo int,
  
price decimal(19, 2)
)
go

insert into prices
values (1, 100.00),
       (
2, 956.48)
go

create trigger tr_priceDelete
  
on prices
   instead
of delete
as
begin
   delete
p
  
from prices p
  
join deleted d
  
on p.priceID = d.priceID
end
go

delete from prices
go

select *
from prices
go


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.
Comments:
NB: Comments will only appear once they have been moderated.