question about order in which queries fire in trigger

I ran into this trigger in a project. Is this tigger firing Delete from
webproducts join Deleted... first? and then Delete from webproducts join
Inserted... second? Or is the ...join Inserted firing first and then the
...deleted?
The table SubDetail gets updated on some column, any column. No deletes
involved. What would constitute deleted if it isn't Delete from webproducts
join Inserted?
----
CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
for update
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
return
---
Thanks,
RichOn Fri, 12 May 2006 15:17:01 -0700, Rich wrote:

>I ran into this trigger in a project. Is this tigger firing Delete from
>webproducts join Deleted... first? and then Delete from webproducts join
>Inserted... second? Or is the ...join Inserted firing first and then the
>...deleted?
>The table SubDetail gets updated on some column, any column. No deletes
>involved. What would constitute deleted if it isn't Delete from webproduct
s
>join Inserted?
(snip code)
Hi Rich,
I'm not sure if I understand all your questions.
However:
1. Statements in a trigger are executed sequentially, top-down (unless
you explicitly change order of execution with GO TO, WHILE, IF or other
control-flow statements).
2. In an UPDATE trigger, the deleted pseudo-table holds the before
update image of all affected rows and the inserted pseudo-table holds
the after update image of all affected rows.
Hugo Kornelis, SQL Server MVP|||Rich (Rich@.discussions.microsoft.com) writes:
> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts
> join Inserted... second?
Yes, statements are executed sequentially.

> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from >
> webproducts join Inserted?
In an UPDATE trigger, both the "inserted" and "deleted" tables are
popoulated. "inserted" holds the after-image data, and "deleted" is
before-image. That is how the row looked like before the update.

> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
Seems funny to run two deletes like that...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your replies. Actually, I left out other stuff from that
trigger which I did understand. However, the explanations provided here did
enlighten me about one thing that I was not aware of, the image of the table
before the action - "Deleted".
I actually do need to capture the before image, and I had no idea how to
describe that. Now I understand. Thank you all for explaining this to me.
Now I understand.
It doesn't solve my immediate problem, but now I think I can figure it out.
Incase anyone cares, my problem is that I am updating this table from a
client app using com ADO and want to retrieve the records affected count. I
f
...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
counts of records affected. But if ...deleted... comes before ...inserted..
.
then
cmd.Execute j, , adExecuteNoRecords
is not returning the count of records affected. But at least now I can
tweak the trigger a little since I have a better idea what is going on.
Thanks again all,
Rich
"Rich" wrote:

> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts join
> Inserted... second? Or is the ...join Inserted firing first and then th
e
> ...deleted?
> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from webproduc
ts
> join Inserted?
> ----
--
> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
> return
> ---
> Thanks,
> Rich|||Let me clarify a few things here.. Rich.. if you don't mind.
Take your case
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
Here first you are joining with deleted table on recordID and then the same
with inserted. And as Hugo and Erland have mentioned, Deleted has the old
image and Inserted has the new image. So this delete with join from deleted
is actually redundant.
There are two cases. One where the update is happening on the column
"RecordID".
In that case you will not be having any row in the table with the value of
"RecordID" that you have in deleted. So the first delete will not delete any
rows (Though I made an assumption here, that RecordID is the PKey :)
And if RecordID is not being updated, then the first delete will delete the
rows and the second delete will try to delete the same rows which will not
happen, so you will be getting rowcount = 0.
What I don't understand is your requirement. Do you want to delete the
updated rows from the table'|||On Fri, 12 May 2006 20:23:01 -0700, Omnibuzz wrote:
(snip)
> So this delete with join from deleted
>is actually redundant.
Hi Omnibuzz,
Good catch!

>What I don't understand is your requirement. Do you want to delete the
>updated rows from the table'
Same here. I hope that Rich will return and explain his requirements
some more.
Hugo Kornelis, SQL Server MVP|||examnotes <Rich@.discussions.microsoft.com> writes:
>Thank you all for your replies. Actually, I left out other stuff from that
>trigger which I did understand. However, the explanations provided here di
d
>enlighten me about one thing that I was not aware of, the image of the tabl
e
>before the action - "Deleted".
>I actually do need to capture the before image, and I had no idea how to
>describe that. Now I understand. Thank you all for explaining this to me.
>Now I understand.
>It doesn't solve my immediate problem, but now I think I can figure it out.
>Incase anyone cares, my problem is that I am updating this table from a
>client app using com ADO and want to retrieve the records affected count.
If
>...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
>counts of records affected. But if ...deleted... comes before ...inserted.
.
>then
>cmd.Execute j, , adExecuteNoRecords
> is not returning the count of records affected. But at least now I can
>tweak the trigger a little since I have a better idea what is going on.
I think you need to skip adExecuteNoRecords and instead loop over
:NextRecordset. Each UPDATE and DELETE causes a rowcount. Obviously,
you want the first: the number of rows inserted. You must loop over
.NextRecordset to get all rowcounts, even if you don't need them.
--
Erland Sommarskog, Stockholm, esquel@.sommarskog.se