Help in Creating Delete Trigger

reyboy · June 9th, 2005, 08:37 PM

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.Supplied

CREATE TRIGGER MRSuppliedDelete ON dbo.mat_tblMaterialsIssuanceDet
FOR Delete
AS
UPDATE mat_tblMaterialsRequestDet
SET 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:

Code:

-------------------------------------------------------
Table: mat_tblMaterialsRequestDet
-------------------------------------------------------
MRNo        nvarchar    10 (Fkey)        |1    |2
StockCode    nvarchar    20            |AA    |AA
PartNo        nvarchar    20            |AAAA    |AAAA
Description    nvarchar    50            |Desc    |Desc
Qty        real        4            |5    |10
Supplied    real        4  (Field to be Updated)|5    |10
ItemID        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    |1
MRNo        nvarchar    10        |1    |2    |2
StockCode    nvarchar    20        |AA    |AA    |AA
PartNo        nvarchar    20        |AAAA    |AAAA    |AAAA
Description    nvarchar    50        |Desc    |Desc    |Desc
Qty        real    4            |5    |5    |5
MRItemID    int    4            |1    |2    |2
MISItemID    int    4 (Incrementing)    |1    |2    |3
-------------------------------------------------------

Thank you guys

Proud To Be Pinoy

happygv · June 15th, 2005, 03:57 PM

Looks like the tables can be normalised, though that is not your concern now.

May be you can explain it in detail with some sample data so as to understand what actually is required in this case. As per going by the trigger sample that you provided here, it is bit confusing to understand what actually is expected.

_________________________
- Vijay G
Strive for Perfection

reyboy · June 15th, 2005, 07:54 PM

Data is actually in Illustration 1. just dont know how to format it here.
Ex:
Table : mat_tblMaterialsIssuanceDet
Fields: MISNo nvarchar 10 (Fkey)
"|" are actually separatorsfor data.
"1" inside or after "|" are actually data.

What is expected:
After deleting a row in tblMaterialsIssuanceDet, mat_tblMaterialsRequestDet.Supplied is updated

CREATE TRIGGER MRSuppliedDelete ON dbo.mat_tblMaterialsIssuanceDet
FOR Delete
AS
UPDATE mat_tblMaterialsRequestDet
SET 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))

problem arises when there are two similar items in mat_tblMaterialsIssuanceDet.

Thanks happygv

Proud To Be Pinoy

happygv · June 16th, 2005, 12:35 PM

What is that you want to update? Say you delete a row with MISNO 1. So I assume you want to minus the sum(QTY) for that MSINO which relates to that MRNO, from the SUPPLIED in the other table. Is that just what you wanted to do?

What kind of relationship exist between those two table? Is that one to many or many to many? If one to many, it makes sense, else I dont see it worth doing it that way.

_________________________
- Vijay G
Strive for Perfection

reyboy · June 18th, 2005, 04:03 AM

They have many to many relationship that is why the trigger isn't working, can't it be done?

Proud To Be Pinoy

happygv · June 18th, 2005, 06:41 PM

I dont understand the logic of having many to many relationship there. If so, which row you wanted to update when you delete multiple rows from the other table, for this you would have multiple rows satisfying the criteria for update? Also why do you have to sum up the values, if you are updating in a many to many setup?

What is the Foriegn key that exists in the table on which DELETE is fired, that links to the PrimaryKey or other unique column in the table which UPDATE is to be done?

I thinks the basic are wrong at the table structure level. Otherwise, update should be done individualy in a many to many setup.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

reyboy · June 19th, 2005, 08:27 PM

Here are the relationship keys
mat_tblMaterialsRequestDet.MrNo = mat_tblMaterialsIssuanceDet.MrNo
mat_tblMaterialsRequestDet.ItemID = mat_tblMaterialsIssuanceDet.MRItemID

Here is what I wanted to do in the app:
There will be many request for materials and I want to process request in just 1 or 2 transactions (1 if all the stocks are available or continue issuing as long as the requested quantity isn't met). I plan to make the trigger my security check by updating the supplied Quantity.

There is no problem when I update mat_tblMaterialsRequestDet.supplied when I am Updating/Inserting, the problem is when I want to delete.

Proud To Be Pinoy

happygv · June 21st, 2005, 06:18 AM

Code:

CREATE TRIGGER MRSuppliedDelete ON dbo.mat_tblMaterialsIssuanceDet 
FOR Delete
AS
UPDATE mat_tblMaterialsRequestDet
SET MD.Supplied = MD.Supplied - D.Qty
FROM mat_tblMaterialsRequestDet MD, DELETED D
WHERE MD.MrNo = D.MrNo
AND MD.MRItemID = D.MRItemID

Hope this helps.

_________________________
- Vijay G
Strive for Perfection