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.

Delay affecting key violation?!

Hello!
I'm having a little problem with SQL Server (this doesn't happen in Access):
Using ADO:
I begin a transaction
I insert a row into table X, but the primary key already exists so(an error
should be raised immediately)
I insert a related row into a related table X.
I do some more things
Hey! Error?! Key violation?! Where? At table X (my first insert)...
uhhhhhhhhh!!!
Is there any way to make SQL Server send an error immediately if the insert
violate the primary key?
I just don't get this :(
Thanks for all your help.
Jorge Carvalho
rdc02271@.yahoo.comset xact_abort on
"Jorge C" wrote:

> Hello!
> I'm having a little problem with SQL Server (this doesn't happen in Access
):
> Using ADO:
> I begin a transaction
> I insert a row into table X, but the primary key already exists so(an erro
r
> should be raised immediately)
> I insert a related row into a related table X.
> I do some more things
> Hey! Error?! Key violation?! Where? At table X (my first insert)...
> uhhhhhhhhh!!!
> Is there any way to make SQL Server send an error immediately if the inser
t
> violate the primary key?
> I just don't get this :(
> Thanks for all your help.
> Jorge Carvalho
> rdc02271@.yahoo.com
>
>|||Thanks but little problem:
If I set transaction mode to explicit the entire transaction will be rolled
back, neglecting my commit or rollback statement.
So what's the point of having explicit transaction mode when you are unable
to: control the error the minute it happens + make necessary changes and try
again without rolling back the transaction?
Thanks.
Jorge Carvalho
rdc02271@.yahoo.com
"Ray D" <RayD@.discussions.microsoft.com> escreveu na mensagem
news:F78827F1-F1BC-4ADF-B25A-F517529B9C12@.microsoft.com...
> set xact_abort on
> "Jorge C" wrote:
>
>|||You are correct that an error should be raised as soon as you insert a
row that violates a constraint. However, what you haven't told us is
the exact "key violation" error message you are getting? Was it the
foreign key or primary key constraint? It seems like the second insert
caused the error so maybe the message is "INSERT statement conflicted
with COLUMN FOREIGN KEY constraint", which might explain why you see it
as an error "at table X".
Also, check if there are any triggers on these tables. Maybe a trigger
is at fault.
David Portas
SQL Server MVP
--|||Hi!
The primary key at the first table is being violated.
Our scheme is like this: (if you have a better system please share :) )
1)The user fills out an invoice (header+details+some other info)
2)Then we try to save the invoice using the latest invoice number+1;but
since we have more one application doing the same with the same database
(multi-user) the insert for the first table may fail (invoice header). So we
need to change the invoice number and try again.
The primary key violation message refers to this first operation.
Thanks.
Jorge Carvalho
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escreveu na mensagem
news:1112358100.042970.307850@.z14g2000cwz.googlegroups.com...
> You are correct that an error should be raised as soon as you insert a
> row that violates a constraint. However, what you haven't told us is
> the exact "key violation" error message you are getting? Was it the
> foreign key or primary key constraint? It seems like the second insert
> caused the error so maybe the message is "INSERT statement conflicted
> with COLUMN FOREIGN KEY constraint", which might explain why you see it
> as an error "at table X".
> Also, check if there are any triggers on these tables. Maybe a trigger
> is at fault.
> --
> David Portas
> SQL Server MVP
> --
>|||That isn't an efficient strategy for generating an incrementing key. Also,
as a matter of design principle it's not a good idea to build a system that
generates errors as part of its correct behaviour.
Max invoice number + 1:
INSERT INTO Invoices (invoice_num,... /* other columns */)
SELECT COALESCE(MAX(invoice_num),0)+1, ... /* other data */
FROM Invoices
If it isn't a problem to have gaps in the sequence of invoice numbers then
you might also consider using the IDENTITY property to generate the
sequence.
Don't forget to declare your table's natural key. Maybe (customer,
invoice_date) for example.
David Portas
SQL Server MVP
--