delete trigger is not working as i want

  • Hi everyone

    i 'm using sql server express 2005 and i have tree triggers for insert delete and update

    İ am making my inserts ,deletes or updates through ADO.NET (so multiple rows in an update or delete or insert)

    i have a sales table and a sales details table and i am making a plus operation on stock when it's an insert and the opposite for deletes(putting back the stocks quantity),

    The insert works fine (when i make an insert all the godds that sold are on the plus values and taht's right), the update is also fine but when it comes to delete ,it updates only the fist stocks that sold and the other rows are untouched

    here's my delete trigger

    ALTER TRIGGER [dbo].[faturagdelete] on [dbo].[fatura]

    for delete

    AS

    declare @stokkod int,@quantity decimal(8,2)

    select @stokkod=stokkod,@quantity=quantity from deleted

    UPDATE stok

    SET stokquantity =stokquantity-@quantity

    FROM stok

    WHERE stok.stokkod =@stokkod

    why did this trigger updated only the First items quantity and leave the rest(the same methot is working on insert trigger bu this ones not)

    i'll be very appreciate for any ideas thanks.

  • Triggers fire once per insert/update/delete, not once per row. Your line here

    select @stokkod=stokkod,@quantity=quantity from deleted

    Will fetch only one of the values from the deleted table, while there are as many as rows that you delete.

    Maybe something more like this... (rough, untested)

    UPDATE stok

    SET stokquantity =stokquantity-deleted.quantity

    FROM stok inner join deleted on

    stok.stokkod = deleted.stokkod

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear friend it worked exactly as i want ,and by the way thanks to your explanation i think i got the logic thank you very very much, it was a lifesaver answer for me 😉

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply