Creating Delete trigger HELP - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating Delete trigger HELP

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


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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources