July 9, 2008 at 6:04 pm
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.
July 10, 2008 at 12:19 am
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
July 10, 2008 at 2:26 pm
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