Connecting Tech Pros Worldwide Help | Site Map

Delete Trigger

  #1  
Old July 24th, 2008, 03:45 PM
Andy Baker
Guest
 
Posts: n/a
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


  #2  
Old July 24th, 2008, 04:45 PM
Plamen Ratchev
Guest
 
Posts: n/a

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

  #3  
Old July 24th, 2008, 10:15 PM
David Portas
Guest
 
Posts: n/a

re: Delete Trigger


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


  #4  
Old July 25th, 2008, 08:55 AM
Andy Baker
Guest
 
Posts: n/a

re: Delete Trigger



"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.orgwrote in message
news:bq2dne4ys_7PchXVnZ2dnUVZ8uOdnZ2d@giganews.com ...
Quote:
"Andy Baker" <abaker@NOSPAMvanputer.comwrote in message
news:V4adnYrYUYXLCRXVnZ2dneKdnZydnZ2d@posted.plusn et...
Quote:
>>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


Closed Thread


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with delete trigger m1st answers 12 February 22nd, 2008 06:14 PM
Create Delete Trigger on Table1 to Update a filed on Table2 Yas answers 3 January 11th, 2008 10:25 PM
how i create a delete trigger that will save the deleted row into another table, like nomi121 answers 1 January 9th, 2007 06:54 AM
ON DELETE trigger blocks delete from my table Naeem Bari answers 4 November 23rd, 2005 02:39 AM
Delete trigger in SQL Server 7 Aidan Whitehall answers 7 July 20th, 2005 03:33 AM