Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rb112978
Starting Member
11 Posts |
Posted - 2005-06-07 : 03:49:58
|
Scenario: when I Insert/Update a certain Stockcode in dbo.mat_tblMaterialsIssuanceDet, a trigger updates the supplied field in the mat_tblMaterialsRequestDet (DONE) See Illustration1: Problem: Help in creating a Delete Trigger that will update mat_tblMaterialsRequestDet.SuppliedCREATE TRIGGER MRSuppliedDelete ON dbo.mat_tblMaterialsIssuanceDet FOR DeleteASUPDATE mat_tblMaterialsRequestDetSET mat_tblMaterialsRequestDet.Supplied = mat_tblMaterialsRequestDet.Supplied - (SELECT SUM(QTY) FROM deleted WHERE MRNo = (SELECT TOP 1 MRNo FROM Deleted) AND itemid = (SELECT TOP 1 mritemid FROM Deleted))WHERE (mat_tblMaterialsRequestDet.itemid = (SELECT mritemid FROM Deleted))AND (mat_tblMaterialsRequestDet.MRNo = (SELECT MRNo FROM Deleted))Illustration1:-------------------------------------------------------Table: mat_tblMaterialsRequestDet-------------------------------------------------------MRNo nvarchar 10 (Fkey) |1 |2StockCode nvarchar 20 |AA |AAPartNo nvarchar 20 |AAAA |AAAADescription nvarchar 50 |Desc |DescQty real 4 |5 |10Supplied real 4 (Field to be Updated)|5 |10ItemID int 4 (Incrementing) |1 |2--------------------------------------------------------------------------------------------------------------Table: mat_tblMaterialsIssuanceDet ------------------------------------------------------- (Data is in this manner because of First-In-First-Out Pricing)MISNo nvarchar 10 (Fkey) |1 |1 |1MRNo nvarchar 10 |1 |2 |2StockCode nvarchar 20 |AA |AA |AAPartNo nvarchar 20 |AAAA |AAAA |AAAADescription nvarchar 50 |Desc |Desc |DescQty real 4 |5 |5 |5MRItemID int 4 |1 |2 |2MISItemID int 4 (Incrementing) |1 |2 |3------------------------------------------------------- Thank you guysProud to be Pinoy |
|
rb112978
Starting Member
11 Posts |
Posted - 2005-06-08 : 04:00:48
|
What should I do? Do I need to change my Trigger or add another field for reference?Proud to be Pinoy |
|
|
|
|
|