Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

delete trigger is not working as i want Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2008 6:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 3, 2011 1:05 PM
Points: 22, Visits: 54
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.
Post #531303
Posted Thursday, July 10, 2008 12:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #531385
Posted Thursday, July 10, 2008 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 3, 2011 1:05 PM
Points: 22, Visits: 54
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 ;)
Post #531967
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse