473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help Automatically Populating Fields base on PK

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
1 1424
ck9663
2,878 Expert 2GB
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

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

Similar topics

0
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
1
by: cloverme | last post by:
Hi, I need help populating a listbox from a database on a webform. I created a an access database with a table, fields and data. Then I created a WebForm in vb.net and added a DropDownList...
8
by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- ...
6
by: Don Sealer | last post by:
I've written this expression for a DLookup function. It works almost alright. What I'm trying to do is type in a description and the ID field (number) populates automatically. It works almost as...
28
by: Siv | last post by:
Hi, If I run the following: strSQL = "Select * FROM Clients;" da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter cb = New OleDb.OleDbCommandBuilder(da) ...
1
by: deercreek | last post by:
I need some help figuring something out. I'm not sure if I heading in the correct direction. What I'm trying to accomplish is the following. I have a table that has id, discrip, and 2 date...
15
by: deercreek | last post by:
I need some help figuring something out. I'm not sure if I heading in the correct direction. What I'm trying to accomplish is the following. I have a table that has id, discrip, and 2 date fields....
0
by: daverskully | last post by:
I have two tables created and want to link two forms created from these tables so that specific fields are populated once one field is selected, but not all fields being populated, with a new record...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.