DELETE and UPDATE Trigger question

Hello
I have a Trigger on a table. Here is the code

ALTER TRIGGER [dbo].[OnOrderDelete]ON [dbo].[orders] AFTERDELETE,UPDATEAS BEGINSET NOCOUNT ON;DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;END

Actually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:
When orders table is updated, the

"SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;"

part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @.ids =(SELECT idfromUPDATED) ")

Is this right? And how can I part UPDATED and DELETED ?

Thanks
Artashes

Because an update is logically a delete and insert combined into one atomic transaction.

And just an FYI - your trigger will fail if you ever try to delete multiple rows with a single statement. You should try:

DELETE FROM files where OrderID IN (SELECT ID FROM deleted)

|||

Thanks for information!

Your way of deleting seems to be more correct!

But you know, my query works!!

Is there any explanation?

|||

Motley has given the explanation. In SQL Server there is no actually 'UPDATE' as you may expect just some modification on a row, instead an UPDATE consists an INSERT followed by a DELETE, so that's why you felt that DELETED is treated as UPDATED.

|||

Iori_Jay, about DELETE and INSERT everything is clear.

I mean why works my query

DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;

Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write

DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);

|||

Try executing DELETE FROM files.

With your trigger, it will fail.

|||

Motley, I don't understand you!

And what am I executing now?

Or you mean only just "DELETE FROM files;" (delete all rows in files?)

|||Yes, if you used the trigger you originally had and tried to delete all the rows in files, the trigger would throw an error. Any delete that caused multiple rows to be deleted would fail.|||

I don't know about all files, but there were situations, when my query deleted 3 rows!

|||

Not within a single statement.

DELETE from files where fileid=1

DELETE from files where fileid=2

would work, but...

DELETE from files where fileid=1 or fileid=2

would fail.

|||

Thanks for info!

But there is no statement like "DELETE from files where fileid=1 or fileid=2" in my query? Am I right?

|||

artashes:

Iori_Jay, about DELETE and INSERT everything is clear.

I mean why works my query

DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;

Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write

DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);

I can't understand why such query works even when you're trying to delete multiple rows--the subquery will return multiple rows and "SELECT @.ids =(SELECT idfrom DELETED)" command will fail as it is trying to assign multiple values to a single variable.However the following query will work as it assigns the last id from deleted table to the variable:

DECLARE @.idsint;
SELECT @.ids =idfrom DELETED;
DELETE FROM filesWHERE OrderId = @.ids;

||| If you wan't I can send you a project where it works, and deletes NOT only the last deleted id.