using SQL Server 2005
I have a table SALES_ITEM, users should be able to input the Primary Keys (ItemNumberSK and InvoiceNumber), and Qty. I want the ItemName and UnitPrice fields to update automatically from the WAREHOUSE table.
I wrote a trigger already but something is wrong with it as I get this error (My code is listed under the error):
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Cannot insert the value NULL into column 'InvoiceNumberSK', table 'prac.dbo.SALES_ITEM'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
---------------------------
OK Help
---------------------------
/* 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 that Automatically Inserts ItemName & UnitPrice From WAREHOUSE table, with the matching ItemNumberSK */
go
CREATE TRIGGER Populate_ItemName_UnitPrice ON SALES_ITEM AFTER INSERT
AS
DECLARE
@ItemNumberSK int,
@ItemName char(50),
@UnitPrice money
SELECT @ItemNumberSK = ItemNumberSK
FROM WAREHOUSE
BEGIN
INSERT INTO SALES_ITEM (ItemName, UnitPrice)
VALUES (@ItemName, @UnitPrice)
END