May 26, 2012 at 8:49 pm
I have a project to compare value from a table, if the value is smaller then the static value say 200, then my trigger will fire to insert a row in another table, I cannot make my triiger work, here is my tabels, my trigger code;
inventory table
inventID (PK)
ProdID (FK)
inventoryQty
PurchaseOrder table
POrdID (PK)
ProdID (FK)
OrdQty
I want to make sure the inventoryQty from inventory table always bigger than say 200, if lower than this value, then my trigger fire to insert a row in PurchaseOrder table, but I cannot make it work
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER InventLow on PurchaseOrder
For update
<Inventory.InvetQty>
for each Row
AS
BEGIN
Declare @InventoyQty Int
Declare @POrdID int
Declare @ProdID int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
if NEW.inventoryQty < 150 then
begin
IF NOT EXISTS (
SELECT 1
FROM Inventory
WHERE ProID = NEW.ProID
)
-- Insert statements for trigger here
BEGIN
INSERT INTO PurchaseOrder
VALUES (NEW.POrdId, NEW.ProdID, 600)
END
end
May 26, 2012 at 9:40 pm
Try it like this:
CREATE TRIGGER InventLow on PurchaseOrder
For update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
INSERT INTO PurchaseOrder
SELECT ins.POrdId, ins.ProdID, 600
FROM Inventory As inv
JOIN inserted As ins
ON inv.ProdID = ins.ProdID
WHERE ins.inventoryQty < 150
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 12:26 am
Thank for reply, I'll test it out and let you know.
May 27, 2012 at 12:30 am
but ss eight,
I do not understand, why do you select 600, it is only a value set up, it is the value I want to insert into the row.
Thanks
May 27, 2012 at 10:20 am
The SELECT is part of the INSERT command. It tells the INSERT what values to insert.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 10:22 am
Maybe this formatting will make it clearer:
CREATE TRIGGER InventLow on PurchaseOrder
For update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
INSERT INTO PurchaseOrder
SELECT ins.POrdId, ins.ProdID, 600
FROM Inventory As inv
JOIN inserted As ins
ON inv.ProdID = ins.ProdID
WHERE ins.inventoryQty < 150
The SELECT subquery replaces the VALUES(..) clause that you were using.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 2:58 am
Thanks so much SS!
but do I need to assign a value to the POrdID?
May 28, 2012 at 3:09 am
SS, I have tried, changed a bit in the table name;
WHERE inv.InventQty < 150 (before you put as ins.InventQty) I specificly lower one product quantity to 100
then I get this error message;
Msg 102, Level 15, State 1, Procedure InventLow, Line 15
Incorrect syntax near '150'.
May 28, 2012 at 3:17 am
SS, I also get red leggie at the end os 150, maybe need to add something behind 150?
May 28, 2012 at 3:18 am
sorry, end of 150 red leggy.
May 28, 2012 at 5:30 am
SS I also have another concern, because the quantity still < 150, will this trigger keep insert?
May 28, 2012 at 5:37 am
weisen678 (5/28/2012)
sorry, end of 150 red leggy.
Sorry, just add an "END" statement on to the end. That should get rid of the dreaded "red leggy".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 5:39 am
weisen678 (5/28/2012)
SS I also have another concern, because the quantity still < 150, will this trigger keep insert?
This will only INSERT a new row for rows that were just inserted with quantity < 150, as you specified. Isn't that what you wanted?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 5:46 am
Oops, I just noticed a mistake in my code. Try it like this instead:
CREATE TRIGGER InventLow on PurchaseOrder
For update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
INSERT INTO PurchaseOrder
SELECT ins.POrdId, ins.ProdID, 600
FROM inserted As ins
WHERE ins.inventoryQty < 150
And NOT EXISTS(
SELECT *
FROM Inventory As inv
WHERE inv.ProdID = ins.ProdID
)
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 11:16 pm
SS, now, I have tried again, this time I get a red leggy under my column name 'inventoryQty', when my mouth over, I get this message,
Invalid column name 'InventoryQty'.
I then defined another line under ' inserted as ins '
inventroy as inv, then the red leggy disappeared, but all the other columns have red leggy.
what I think is
the 'InventoryQty' column belong to the inventroy table, but when we insert, we inserted into the purchaseOrder table, there should be somewhere to define the inventory table, then the column will be recognised.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply