trigger and INSERTED

10 Dec 2005 12:44 AM

shank

I'm trying to setup a trigger. When a customer buys a package deal of goods,
I want the trigger to populate OrderDetails with all the extra product. I
realize that I have to use the INSERTED table to analyze one specific order,
but when I drop the below code into place I get an error that INSERTED does
not exist. How do I get around this?

thanks!

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
DECLARE @RC int
SET @RC = (SELECT COUNT(*) AS RC
FROM ItemKey INNER JOIN Inserted ON
ItemKey.KeyNumber = Inserted.OrderNo
GROUP BY Inserted.OrderNo, ItemKey.KeyNumber)

BEGIN

IF @RC > 0

INSERT INTO OrderDetails
(OrderID, OrderNo, Description, Qty, PriceEach, PriceLine, STPrice)
(SELECT OrderDetails.OrderID, ItemKey.OrderNo, ItemStock.Descrip,
ItemKey.Qty, 0, 0, 0
FROM ItemKey INNER JOIN OrderDetails ON
ItemKey.KeyNumber = Inserted.OrderNo INNER JOIN ItemStock ON
ItemKey.OrderNo = ItemStock.OrderNo)

END

10 Dec 2005 12:55 AM

Mike Epprecht (SQL MVP)

Hi

You have no join on "inserted" in your INSERT INTO statement

Maybe "OrderDetails" table is wrong and should be "Inserted" based on your
first select.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"shank" <sh***@tampabay.rr.com> wrote in message
news:usGJ1LS$FHA.2708@TK2MSFTNGP12.phx.gbl...
> I'm trying to setup a trigger. When a customer buys a package deal of
> goods, I want the trigger to populate OrderDetails with all the extra
> product. I realize that I have to use the INSERTED table to analyze one
> specific order, but when I drop the below code into place I get an error
> that INSERTED does not exist. How do I get around this?
>
> thanks!
>
> -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> DECLARE @RC int
> SET @RC = (SELECT COUNT(*) AS RC
> FROM ItemKey INNER JOIN Inserted ON
> ItemKey.KeyNumber = Inserted.OrderNo
> GROUP BY Inserted.OrderNo, ItemKey.KeyNumber)
>
> BEGIN
>
> IF @RC > 0
>
> INSERT INTO OrderDetails
> (OrderID, OrderNo, Description, Qty, PriceEach, PriceLine, STPrice)
> (SELECT OrderDetails.OrderID, ItemKey.OrderNo, ItemStock.Descrip,
> ItemKey.Qty, 0, 0, 0
> FROM ItemKey INNER JOIN OrderDetails ON
> ItemKey.KeyNumber = Inserted.OrderNo INNER JOIN ItemStock ON
> ItemKey.OrderNo = ItemStock.OrderNo)
>
> END
>

10 Dec 2005 1:13 AM

shank

Thanks!