By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Help Automatically Populating Fields base on PK

P: 8
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
Nov 12 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
You never store any value in @ItemName and @UnitPrice

Expand|Select|Wrap|Line Numbers
  1.  
  2.     INSERT INTO SALES_ITEM (ItemName, UnitPrice)
  3.     VALUES (@ItemName, @UnitPrice)
  4.  
  5.  

-- CK
Nov 12 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.