Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 9th, 2005, 08:37 PM
Authorized User
 
Join Date: Jul 2003
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help in Creating Delete Trigger

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
__________________
Proud To Be Pinoy
 
Old June 15th, 2005, 03:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 15th, 2005, 07:54 PM
Authorized User
 
Join Date: Jul 2003
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 16th, 2005, 12:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 18th, 2005, 04:03 AM
Authorized User
 
Join Date: Jul 2003
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Proud To Be Pinoy
 
Old June 18th, 2005, 06:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 19th, 2005, 08:27 PM
Authorized User
 
Join Date: Jul 2003
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 21st, 2005, 06:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to delete file System.IO.Delete error maricar C# 13 March 14th, 2014 06:50 AM
delete query using trigger prashant_telkar SQL Language 1 June 19th, 2007 10:39 AM
Creating stored procedure with trigger (HOWTO ..) AzlanAziz SQL Language 0 January 16th, 2007 04:34 AM
Delete record without creating overheads Picabrillo BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 July 30th, 2004 09:30 AM
Update, insert and delete Trigger khautinh SQL Server 2000 2 September 17th, 2003 11:45 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.