Delete Trigger

[Andy Baker]

I have set up a trigger that updates a table in another part of may database
whenever a row is deleted - of the form
CREATE TRIGGER t_Delete ON tblOrderDetails AFTER DELETE
AS
SELECT @Quantity = Quantity FROM DELETED
etc etc
This works fine if I delete only one row from tblOrderDetails at a time, but
if I run a DELETE query that deletes more than one row, the correct rows are
deleted but my trigger only gets executed once, where I want it to be
executed for each deleted row. Is this normal behaviour, or am I doing
somthing wrong? Thanks in advance.

Andy Baker

[Plamen Ratchev]

Re: Delete Trigger

The trigger fires once for the delete statement but you have access to all
deleted rows in the Deleted logical table. You have to write your code to
handle multiple rows. For example, in your select statement you can use SUM
to get the summary quantity for all deleted rows:

SELECT @Quantity = SUM(Quantity)
FROM Deleted;

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[David Portas]

Re: Delete Trigger

"Andy Baker" <abaker@NOSPAMv anputer.comwrot e in message
news:V4adnYrYUY XLCRXVnZ2dneKdn ZydnZ2d@posted. plusnet...

>I have set up a trigger that updates a table in another part of may
>database whenever a row is deleted - of the form
CREATE TRIGGER t_Delete ON tblOrderDetails AFTER DELETE
AS
SELECT @Quantity = Quantity FROM DELETED
etc etc
This works fine if I delete only one row from tblOrderDetails at a time,
but if I run a DELETE query that deletes more than one row, the correct
rows are deleted but my trigger only gets executed once, where I want it
to be executed for each deleted row. Is this normal behaviour, or am I
doing somthing wrong? Thanks in advance.
>
Andy Baker
>
>

The only thing you were doing wrong was to assume that the trigger would be
executed for each row. Triggers execute once per statement, not per row. So
you need to change your code reflect that. Unfortunately we don't know what
your trigger does with the value @Quantity so it's hard to advise you.

--
David Portas

[Andy Baker]

Re: Delete Trigger


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.orgwrote in message
news:bq2dne4ys_ 7PchXVnZ2dnUVZ8 uOdnZ2d@giganew s.com...

"Andy Baker" <abaker@NOSPAMv anputer.comwrot e in message
news:V4adnYrYUY XLCRXVnZ2dneKdn ZydnZ2d@posted. plusnet...

>>I have set up a trigger that updates a table in another part of may
>>database whenever a row is deleted - of the form
>CREATE TRIGGER t_Delete ON tblOrderDetails AFTER DELETE
>AS
>SELECT @Quantity = Quantity FROM DELETED
>etc etc
>This works fine if I delete only one row from tblOrderDetails at a time,
>but if I run a DELETE query that deletes more than one row, the correct
>rows are deleted but my trigger only gets executed once, where I want it
>to be executed for each deleted row. Is this normal behaviour, or am I
>doing somthing wrong? Thanks in advance.
>>
>Andy Baker
>>
>>

>
The only thing you were doing wrong was to assume that the trigger would
be executed for each row. Triggers execute once per statement, not per
row. So you need to change your code reflect that. Unfortunately we don't
know what your trigger does with the value @Quantity so it's hard to
advise you.
>
--
David Portas
>

Thanks - that clears it up. I have changed my code to account for it.

Andy Baker