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]

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]

"Andy Baker" <ab****@NOSPAMvanputer.comwrote in message
news:V4******************************@posted.plusn et...

>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]

"David Portas" <RE****************************@acm.orgwrote in message
news:bq******************************@giganews.com ...

"Andy Baker" <ab****@NOSPAMvanputer.comwrote in message
news:V4******************************@posted.plusn et...

>>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