How Inserted and Deleted Tables Work
Published: Aug 20, 2017
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron