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?)
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 writeDELETE 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.
