Re: Trigger not updating in all conditions

Ashik (Ashik@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
INSERT TRIGGER IN PRORDER
INSERT INTO MATERIALS
SELECT ORDNUM,MATERIAL,CONSUPTION,CONSUPTION*ORDQTY,CON_UNIT FROM
INSERTED,PRORDER
WHERE INSERTED.PRODUCT=PRORDER.PRODUCT

SO MATERIALS TABLE
ORDNUM MATERIALS CONS QTY UNIT
100 REDFAB 0.5 100 KG
100 REBBUTTON 6 1200 PCS
100 COLLAR-S 1 200 PCS
100 THREAD 250 50000 MTR
100 POLYBAG 1 200 PCS
101 REDFAB 0.5 100 KG
101 REBBUTTON 6 1200 PCS
101 COLLAR-M 1 200 PCS
101 THREAD 250 50000 MTR
101 POLYBAG 1 200 PCS


TRIGGER FOR INSERT,UPDATE,DELETE IN MATERIALS TABLE

IF (SELECT * FROM DELETED)
BEGIN
UPDATE S SET S.REQQTY=S.REQQTY-D.QTY
FROM DELETED D,STOCK S WHERE D.MATERIALS=S.MATERIALS
END
IF (SELECT * FROM INSERTED)
BEGIN
UPDATE S SET S.REQQTY=S.REQQTY+I.QTY
FROM INSERTED I, STOCK S WHERE I.MATERIALS=S.MATERIALS
END

While the UPDATE FROM syntax is handy at many times, one has to be
aware of that it permits you to do silly things. If there for a
row in Stock, there can only be one row in INSERTED with the
same value in MATERIALS, the above is fine. But if there are multiple
matches, the result of the update is not deterministic.

It's difficult to read your lists, and I can't even see a semi-definition
of the STOCK table, so I can't give any definitive diagnosis. I would
suggest that you compose a script that includes:

o CREATE TABLE statements for the tables (don't forget key definitions)
o INSERT statments with your sample data that demonstrates the problem.
o The actual CREATE TRIGGER text.
o The expected result.

And please test the script before you post it to make sure that it
demonstrates the problem you are seeing. It such much easier to read
the actual code, and it's also easier to debug and test new versions.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Quantcast