List of PKs for Trigger - 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)

List of PKs for Trigger

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-11-09 : 05:38:13

I want a list of all the PKs in either inserted or deleted - its for an INSERT, UPDATE, DELETE trigger than needs to "worry" about changes in Quantity on order items

Which of these do you prefer?


INSERT INTO @tblUpdatedOrders
(
	T_OrderID, 
	T_ItemNo
)
SELECT DISTINCT -- (1)
	[T_OrderID] = COALESCE(I.OrderID, D.OrderID)
	[T_ItemNo] = COALESCE(I.ItemNo, D.ItemNo)
FROM	inserted I
	FULL OUTER JOIN deleted D
		 ON D.OrderID = I.OrderID
		AND D.ItemNo = I.ItemNo

(1) - is the DISTINCT redundant because of the FULL OUTER JOIN?

or


INSERT INTO @tblUpdatedOrders
(
	T_OrderID,
	T_ItemNo
)
SELECT	[T_OrderID] = OrderID, 
	[T_ItemNo] = ItemNo
FROM	inserted
UNION	-- Removal of Duplicates is REQUIRED
SELECT	[T_OrderID] = OrderID, 
	[T_ItemNo] = ItemNo
FROM	deleted

the intention is that the temporary table is then used to update, say, the Stock Level (in practice we need to update some other tables too):


UPDATE	U
SET
	-- Calculated Stock Level adjustment from Order Items
	[QtyOnHand]	= COALESCE(QtyOnHand, 0)
			+ COALESCE(I.Qty, 0)
			- COALESCE(D.Qty, 0)
FROM	@tblUpdatedOrders -- (2)
	JOIN dbo.OrderItem AS OI
		 ON OI.OrderID = T_OrderID
		AND OI.ItemNo = T_ItemNo
	JOIN dbo.StockLevel AS U
		 ON U.ProductCode = OI.ProductCode
	LEFT OUTER JOIN deleted D
		 ON D.OrderID = T_OrderID
		AND D.ItemNo = T_ItemNo
	LEFT OUTER JOIN inserted I
		 ON I.OrderID = T_OrderID
		AND I.ItemNo = T_ItemNo
-- Only update if the Qty has changed
WHERE	COALESCE(I.Qty, 0) <> COALESCE(D.Qty, 0)

(2) Would I be better off just using a nested sub-query here? If so Version-1 or Version-2?

Thanks

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-09 : 06:31:46

Your update might have problems if you have multiple orders updated on the same product code.
Something like (put the derived table into your table variable if you wish.

UPDATE U
SET
-- Calculated Stock Level adjustment from Order Items
[QtyOnHand] = COALESCE(QtyOnHand, 0)
+ COALESCE(a.Qty, 0)
from StockLevel AS U
join
(
select OI.ProductCode, qty = sum(coalesce(i.Qty,0) - sum(coalesce(d.Qty,0)
from deleted D
full outer join
inserted I
ON D.OrderID = I.OrderID
AND D.ItemNo = I.ItemNo
join OrderItem AS OI
ON OI.OrderID = coalesce(D.OrderID, I.D.OrderID)
AND OI.ItemNo = coalesce(D.ItemNo, I.D.ItemNo)
) a
on a.ProductCode = U.ProductCode

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-11-10 : 07:41:05

Cheers Nigel, FULL OUTER JOIN it is then ...

Point noted about SUMming them, thanks.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources