delete trigger is not working as i want

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Express » delete trigger is not working as i want

delete trigger is not working as i want

Rate Topic

Display Mode

Topic Options

Author

Message

delta dirac

Posted Wednesday, July 09, 2008 6:04 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 03, 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

GilaMonster

Posted Thursday, July 10, 2008 12:19 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 38,099, Visits: 30,392

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

delta dirac

Posted Thursday, July 10, 2008 2:26 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 03, 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 »

Permissions