Multirow Control Total Update Trigger

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)

Multirow Control Total Update Trigger

Author

Topic

Ticks
Starting Member

10 Posts

Posted - 2005-12-19 : 07:40:46

Could someone please take a look at this. Am I missing the point on triggers? When you run this the idea is that after creating the transaction files it up dates the STKQty on the transaction files to 5 for each line. On update of STKQty, tHe trigger should take the existing Qty in stock and add the total increase to give a new qty however it only works on multirow updates when only single partcodes and types are present - in this example like PartCode A type 2, Partcode B and Partcode C. When we have multiple transactions for the same partcode being updated like Partcode A Type 1 x 3 rows it calculates the total increase but then adds it to the qty in the stock file several times dependent upon the number of rows. In this case Qty = 10 + (15 * 3 rows) = 55 where correct total is 25. Please take a look and advise ASAP. Regards Paul

USE BASE_Test
DROP TABLE tblStock
GO
DROP TABLE tblTPOLines
GO
CREATE TABLE tblStock
(PartCode nVarChar(50) NOT NULL,
Type tinyint NOT NULL,
SiteID tinyint NOT NULL,
Qty SmallInt NOT NULL,
CostPrice money NOT NULL,
BackOrderQty smallint NULL)

GO

CREATE TABLE tblTPOLines
(tsID timestamp NOT NULL,
TPOLineID int NOT NULL IDENTITY PRIMARY KEY,
PartCode nVarChar(50) NOT NULL,
Type tinyint NOT NULL,
CostPrice money NOT NULL,
TPOQty SmallInt NOT NULL,
STKQty smallInt NOT NULL,
Stock TinyInt NOT NULL,
SiteID_To tinyint NOT NULL)
GO

CREATE TRIGGER tblTPOLines_UPDATE
ON tblTPOLines
FOR UPDATE
AS
--Trigger valid for multirow and single row inserts
--and optimal for single row inserts

IF UPDATE (STKQty)
BEGIN
UPDATE tblStock
SET tblStock.BackOrderQty = tblStock.BackOrderQty -
ISNULL((SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO),0),
tblStock.Qty = tblStock.Qty +
ISNULL((SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO),0),
tblStock.CostPrice = ((tblStock.Qty * tblStock.CostPrice)+
(SELECT SUM((inserted.STKQty * inserted.CostPrice) -
(deleted.STKQty * deleted.CostPrice))
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO)) /
(tblStock.Qty +
(SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND inserted.TPOLineID = deleted.TPOLineID
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO))
WHERE tblStock.PartCode IN
(SELECT tblStock.PartCode FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0)
AND tblStock.Type IN
(SELECT tblStock.Type FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0)
AND tblStock.SiteID IN
(SELECT tblStock.SiteID FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0)

UPDATE tblStock
SET tblStock.BackOrderQty = tblStock.BackOrderQty -
ISNULL((SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO),0),
tblStock.Qty = tblStock.Qty +
ISNULL((SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO),0)
WHERE tblStock.PartCode IN
(SELECT tblStock.PartCode FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) = 0)
AND tblStock.Type IN
(SELECT tblStock.Type FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) = 0)
AND tblStock.SiteID IN
(SELECT tblStock.SiteID FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) = 0)

END
GO

INSERT INTO tblStock (Partcode, Type, SiteID,Qty,CostPrice,BackOrderQty)
VALUES ('A',1,1,10,4.00,13)
go

INSERT INTO tblStock (Partcode, Type, SiteID,Qty,CostPrice,BackOrderQty)
VALUES ('B',1,1,10,5.00,5)
go

INSERT INTO tblStock (Partcode, Type, SiteID,Qty,CostPrice,BackOrderQty)
VALUES ('A',2,1,10,6.00,14)
go

INSERT INTO tblStock (Partcode, Type, SiteID,Qty,CostPrice,BackOrderQty)
VALUES ('C',1,1,10,7.00,50)
go

SELECT * FROM tblStock
ORDER BY Partcode,Type
GO

INSERT INTO tblTPOLines (PartCode,Type,CostPrice,TPOQty,STKQty,Stock,SiteID_To)
VALUES ('A',1,6.00,5,0,1,1)
go

INSERT INTO tblTPOLines (PartCode,Type,CostPrice,TPOQty,STKQty,Stock,SiteID_To)
VALUES ('A',1,6.00,5,0,1,1)
go

INSERT INTO tblTPOLines (PartCode,Type,CostPrice,TPOQty,STKQty,Stock,SiteID_To)
VALUES ('A',1,6.00,3,0,1,1)
go
INSERT INTO tblTPOLines (PartCode,Type,CostPrice,TPOQty,STKQty,Stock,SiteID_To)
VALUES ('A',2,6.00,14,0,1,1)
go
INSERT INTO tblTPOLines (PartCode,Type,CostPrice,TPOQty,STKQty,Stock,SiteID_To)
VALUES ('B',1,15.00,5,0,1,1)
go

INSERT INTO tblTPOLines (PartCode,Type,CostPrice,TPOQty,STKQty,Stock,SiteID_To)
VALUES ('C',1,90.00,50,0,1,1)
go

SELECT * FROM tblTPOLines
ORDER BY Partcode,Type
GO

UPDATE tblTPOLines
SET STKQty = 5
GO

SELECT * FROM tblStock
ORDER BY Partcode,Type

GO

SELECT * FROM tblTPOLines
ORDER BY Partcode,Type

GO

Needs Help

Ticks
Starting Member

10 Posts

Posted - 2005-12-19 : 10:57:22

Anyone cut and pasted the code in order to run this? I could really do with some advise on where my problem lies?

Regards
Paul

Needs Help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-19 : 11:53:53

I tried, it's too hard to read. An unformatted statement with that many subqueries and derived tables is just too hard to figure out (without getting paid for it

)

Just to understand the issue, you want to store (and maintain) the total item quantity with each item? Meaning if you have ten items you want to store "10" with each of the ten items? Is that because figuring that out on the fly is too costly?

Be One with the Optimizer
TG

cfederl
Starting Member

26 Posts

Posted - 2005-12-19 : 15:59:21

Try something like the below trigger. You will need to add some more statements to insure that backorder quantity is never negative and calculating the average costs.

create TRIGGER tblTPOLines_TUA
ON tblTPOLines FOR UPDATE
AS
set nocount on
set xact_abort on
if 0 = (select count(*) from inserted ) -- No rows updated
RETURN

UPDATE tblStock
SET tblStock.Qty = tblStock.Qty
+ COALESCE( StockInserted.STKQty , 0 )
- COALESCE( StockDeleted.StkQty , 0 )
, tblStock.BackOrderQty = tblStock.BackOrderQty
- COALESCE( StockInserted.STKQty , 0 )
+ COALESCE( StockDeleted.StkQty , 0 )
FROM tblStock
-- Any Stock that has been changed
JOIN (Select distinct inserted.Partcode
, inserted.Type
, inserted.SiteID_TO
from inserted
UNION ALL
Select distinct deleted.Partcode
, deleted.Type
, deleted.SiteID_TO
from deleted
) as Changes
ON tblStock.Partcode = Changes.PartCode
and tblStock.Type = Changes.Type
and tblStock.SiteID = Changes.SiteID_TO
LEFT OUTER JOIN
(select inserted.Partcode
, inserted.Type
, inserted.SiteID_TO as SiteId
, SUM ( inserted.STKQty ) as STKQty
, SUM ( inserted.STKQty * inserted.CostPrice) as CostPrice
FROM inserted
GROUP BY inserted.Partcode
, inserted.Type
, inserted.SiteID_TO
) As StockInserted
ON tblStock.Partcode = StockInserted.PartCode
and tblStock.Type = StockInserted.Type
and tblStock.SiteID = StockInserted.SiteID
LEFT OUTER JOIN
(select deleted.Partcode
, deleted.Type
, deleted.SiteID_TO as SiteId
, SUM ( deleted.STKQty ) as STKQty
, SUM ( deleted.STKQty * deleted.CostPrice) as CostPrice
FROM deleted
GROUP BY deleted.Partcode
, deleted.Type
, deleted.SiteID_TO
) As StockDeleted
ON tblStock.Partcode = StockDeleted.PartCode
and tblStock.Type = StockDeleted.Type
and tblStock.SiteID = StockDeleted.SiteID

Carl Federl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources