How can I get the value from Product_id in Buy that is primarykey and insert it in Product_id in

How can I get the value from Product_id in Buy that is primarykey and insert it in Product_id in Product that is ForeignKey?

My tables look like the following.

table Buy
buy_id identity int PK
invoicenumber int

table Product
Price int
Articlenumber int
Description varchar
Product_id PK
buy_id FK

my code-

CREATE PROCEDURE anotherBuy
AS
BEGIN
INSERT INTO Buy(InvoiceNumber) VALUES (50);
/*Get Product_id*/
INSERT INTO Product(Price, ArticleNumber, Description, Buy_id) VALUES (50, 12, 'tv Sony 50' /*, Product_id*/);
END

CREATE function nyttK?p()
RETURNS int
BEGIN
DECLARE @.buyId as int;
SET @.buyId = 1;
INSERT INTO K?p(Fakturanummer) VALUES (@.buyId);
RETURN /*K?p_id*/ 1
END;*/

/*DECLARE @.buyId as int;*/
/*SET @.buyId = nyttK?p();*/

/*INSERT INTO Produkt (Pris, Artikelnummer, beskrivning) VALUES (30, 101, 'tv');*/

KE

The following query may help you to get idea, In function you can't do any insert/update/delete operations..

Code Snippet

Create table Buy

(

buy_id int identity(1,1) Primary Key,

invoicenumber int

)

Create table Product

(

Product_id int Identity(1,1) Primary Key,

buy_id int references Buy(buy_id),

Price int,

Articlenumber int,

Description varchar(100)

)

Go

Declare @.Buy_id int;

Insert Into Buy Values(50);

--Store the Inserted Buy id in variable

Set @.Buy_id = Scope_Identity();

--Reuse the variable on other quires

Insert Into Product Values(@.Buy_id, 100, 8736, 'First Product');

Insert Into Product Values(@.Buy_id, 1000, 8746, 'Secods Product');

Insert Into Product Values(@.Buy_id, 1000, 4346, 'Third Product');

select * from Buy

Select * from Product

|||

Thank you for that answer.

You helped me with the funktion Scope_Identity();.

My problem is now that I want to execute a trigger that insert the value from the primarykey. The problem is that if I write

DECLARE @.buyId int;

SET @.buyId = Scope_Identity();

outside the trigger. the compiler says that I must declare @.buyId. If I write inside the trigger, like the code below, Scope_Identity(); will return null.

NSERT INTO Buy(InvoiceNumber) VALUES (80);

SELECT InvoiceNumber, Buy_id FROM Buy;

DROP TRIGGER BuyTrigger

CREATE TRIGGER BuyTrigger

ON Buy

FOR INSERT AS

BEGIN

DECLARE @.buyId int;

SET @.buyId = Scope_Identity();

SELECT @.buyId

INSERT INTO Product (Price, ArticleNumber, Description, Buy_id) VALUES (50, 12, 'tv Sony 50', 50);

END

Is it possible to solv with a trigger, or?

|||

Yes. Once the new/next insert statement called on the current scope the value on the Scope_Identity() will be reset.

Can you tell more about your requirement. I can't able to understand what exactly you are trying to do.. pls explain bit more..

|||

Hello!

I have solved the problem with a procedure (with a refrence variable) instead. Look below.

You seem to have very good knowledge Manivannan.D.Sekaran. Can I ask you where you work?

-Write to table Product and get Primary key value --

CreateProcedure insert_buy (@.buy_id int OUT)

AS

DECLARE @.buyId int;

BEGIN

INSERTINTO Buy(InvoiceNumber)VALUES(8);

SELECT InvoiceNumber, Buy_id FROM Buy

SET @.buy_id =Scope_Identity();

END

Write to Product -

CREATEPROCEDURE insert_Product(@.buy_id int)

AS

BEGIN

INSERTINTO Product(Price, ArticleNumber, Description, Buy_id)VALUES(50, 12,'tv Sony 50', @.buy_id);

SELECT Price, ArticleNumber, Description, Buy_id FROM Product;

END

-- Start a buy --

DECLARE @.x int

execute insert_buy @.x OUT;

IF @.x > 0

BEGIN

execute insert_Product @.x;

END

ELSE

BEGIN

PRINT'buyID is 0';

END