Delete Trigger

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

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

re: Delete Trigger

"Andy Baker" <abaker@NOSPAMvanputer.comwrote in message
news:V4adnYrYUYXLCRXVnZ2dneKdnZydnZ2d@posted.plusn et...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

re: Delete Trigger

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.orgwrote in message
news:bq2dne4ys_7PchXVnZ2dnUVZ8uOdnZ2d@giganews.com ...Thanks - that clears it up. I have changed my code to account for it.

Andy Baker