November 16, 2008 at 10:00 am
WAREHOUSE acts as an inventory.
The Trigger should update the WAREHOUSE.ItemQty column. After testing the trigger I have came up with does not update the WAREHOUSE table correctly the math is off. Also the WAREHOUSE table is automatically populated by a trigger when the items are purchased. Here is my code:
/* Creates Trigger that Automatically Updates WAREHOUSE.ItemQty after an sales transaction*/
go
CREATE TRIGGER UpdateWarehouseQty ON SALES_ITEM AFTER INSERT
AS
BEGIN
UPDATE WAREHOUSE
SET WAREHOUSE.ItemQty = (SELECT w.ItemQty - si.Qty FROM SALES_ITEM si WHERE w.ItemNumberSK = si.ItemNumberSK)
FROM WAREHOUSE w
JOIN SALES_ITEM si ON w.ItemNumberSK = si.ItemNumberSK
END
* Creates SALES_ITEM table */
CREATE TABLE SALES_ITEM(
InvoiceNumberSK int NOT NULL,
ItemNumberSK int NOT NULL,
ItemName char(50) NULL,
Qty numeric(7, 0) NOT NULL,
UnitPrice money NULL,
ExtendedPrice AS CASE
WHEN UnitPrice > 0 AND Qty > 0 Then UnitPrice * Qty
END
CONSTRAINT Sales_ItemPK PRIMARY KEY (InvoiceNumberSK, ItemNumberSK),
CONSTRAINT Sales_ItemInvoiceNumberFK FOREIGN KEY (InvoiceNumberSK) REFERENCES SALES (InvoiceNumberSK),
CONSTRAINT Sales_ItemItemNumberSKFK FOREIGN KEY (ItemNumberSK) REFERENCES WAREHOUSE (ItemNumberSK)
);
/* Creates WAREHOUSE table */
CREATE TABLE WAREHOUSE(
ItemNumberSK int NOT NULL,
ItemName char(50) NULL,
ItemUnitPrice money NULL,
ItemQty numeric(7, 0) NULL,
CONSTRAINT WarehousePK PRIMARY KEY (ItemNumberSK),
CONSTRAINT WarehouseFK FOREIGN KEY (ItemNumberSK) REFERENCES ITEM_PURCHASE (ItemNumberSK)
);
/* Creates Trigger to Automatically Populate WAREHOUSE table;
Also WAREHOUSE.ItemUnitPrice is calculated to include a 20% Mark-up for profit */
go
CREATE TRIGGER insert_WAREHOUSE ON ITEM_PURCHASE AFTER INSERT
AS
BEGIN
INSERT INTO WAREHOUSE (ItemNumberSK, ItemName, ItemQty, ItemUnitPrice)
SELECT ItemNumberSK, ItemName, Quantity, (Quantity / (LocalCurrencyAmt * ExchangeRate * Quantity)) * 1.20
from inserted;
END
November 16, 2008 at 4:20 pm
ITHELP85 (11/16/2008)
WAREHOUSE acts as an inventory.The Trigger should update the WAREHOUSE.ItemQty column. After testing the trigger I have came up with does not update the WAREHOUSE table correctly the math is off. Also the WAREHOUSE table is automatically populated by a trigger when the items are purchased. Here is my code:
/* Creates Trigger that Automatically Updates WAREHOUSE.ItemQty after an sales transaction*/
go
CREATE TRIGGER UpdateWarehouseQty ON SALES_ITEM AFTER INSERT
AS
BEGIN
UPDATE WAREHOUSE
SET WAREHOUSE.ItemQty = (SELECT w.ItemQty - si.Qty FROM SALES_ITEM si WHERE w.ItemNumberSK = si.ItemNumberSK)
FROM WAREHOUSE w
JOIN SALES_ITEM si ON w.ItemNumberSK = si.ItemNumberSK
END
* Creates SALES_ITEM table */
CREATE TABLE SALES_ITEM(
InvoiceNumberSK int NOT NULL,
ItemNumberSK int NOT NULL,
ItemName char(50) NULL,
Qty numeric(7, 0) NOT NULL,
UnitPrice money NULL,
ExtendedPrice AS CASE
WHEN UnitPrice > 0 AND Qty > 0 Then UnitPrice * Qty
END
CONSTRAINT Sales_ItemPK PRIMARY KEY (InvoiceNumberSK, ItemNumberSK),
CONSTRAINT Sales_ItemInvoiceNumberFK FOREIGN KEY (InvoiceNumberSK) REFERENCES SALES (InvoiceNumberSK),
CONSTRAINT Sales_ItemItemNumberSKFK FOREIGN KEY (ItemNumberSK) REFERENCES WAREHOUSE (ItemNumberSK)
);
/* Creates WAREHOUSE table */
CREATE TABLE WAREHOUSE(
ItemNumberSK int NOT NULL,
ItemName char(50) NULL,
ItemUnitPrice money NULL,
ItemQty numeric(7, 0) NULL,
CONSTRAINT WarehousePK PRIMARY KEY (ItemNumberSK),
CONSTRAINT WarehouseFK FOREIGN KEY (ItemNumberSK) REFERENCES ITEM_PURCHASE (ItemNumberSK)
);
/* Creates Trigger to Automatically Populate WAREHOUSE table;
Also WAREHOUSE.ItemUnitPrice is calculated to include a 20% Mark-up for profit */
go
CREATE TRIGGER insert_WAREHOUSE ON ITEM_PURCHASE AFTER INSERT
AS
BEGIN
INSERT INTO WAREHOUSE (ItemNumberSK, ItemName, ItemQty, ItemUnitPrice)
SELECT ItemNumberSK, ItemName, Quantity, (Quantity / (LocalCurrencyAmt * ExchangeRate * Quantity)) * 1.20
from inserted;
END
I am confused by your math, and the fact that you have additional fields in the trigger that don't exist in your table definition (LocalCurrencyAmt and ExchangeRate). You really need to provide sample data for the table, and what the expected results in the Warehouse table would be when the data is entered into the Warehouse table.
November 16, 2008 at 7:47 pm
The biggest problem I see is that your trigger is updating the WAREHOUSE table for every record in the SALES_ITEM table - not just the records that were just inserted.
You should be using INSERTED instead of SALES_ITEM in your trigger.
CREATE TRIGGER UpdateWarehouseQty ON SALES_ITEM AFTER INSERT
AS
BEGIN
UPDATE WAREHOUSE
SET WAREHOUSE.ItemQty = (SELECT w.ItemQty - si.Qty FROM SALES_ITEM si WHERE w.ItemNumberSK = si.ItemNumberSK)
FROM WAREHOUSE w
JOIN INSERTED si ON w.ItemNumberSK = si.ItemNumberSK
END
November 17, 2008 at 5:37 pm
This should work for both single and multiple row inserts into the sales table.
------------------------------------------------------------------------------------
ALTER TRIGGER UpdateWarehouseQty ON SALES_ITEM AFTER INSERT
AS
BEGIN
UPDATE WAREHOUSE
SET WAREHOUSE.ItemQty = warehouse.ItemQTY-dt.qty
FROM (select inserted.itemNumberSK, sum(qty) as qty
from inserted
group by inserted.itemNumberSK
) as dt
WHERE warehouse.itemNumberSK = dt.ItemNumberSK
END
go
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply