Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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

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

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

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

create trigger tr_priceDelete
on prices
of delete
from prices p
join deleted d
on p.priceID = d.priceID

delete from prices

select *
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.