insert values to two tables (Buy and Product) simu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert values to two tables (Buy and Product) simu

Author

Topic

hunkieGuy
Starting Member

5 Posts

Posted - 2007-06-25 : 10:25:05

Hello!

I wonder if anybody can help me with the following problem.

I want to insert values to two tables (Buy and Product) simultainasly ie i want the foreign key in Product to have the same value the primary key have in Buy.

Regards KE

KE

m_weltm
Starting Member

8 Posts

Posted - 2007-06-25 : 12:37:46

You could try writing a trigger on Insert for either Buy or Product.

CREATE TRIGGER trProductInsert
ON Product
AFTER INSERT
AS
SET IDENTITY_INSERT Buy ON
INSERT INTO Buy(primary_key_name)
SELECT primary_key_name
FROM Inserted
SET IDENTITY_INSERT Buy OFF

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-25 : 13:49:31

Put both the inserts in the same stored proc one after another (in the appropriate order). No need for a trigger.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hunkieGuy
Starting Member

5 Posts

Posted - 2007-06-26 : 02:39:58

Oki,

but, How do I write the insert into statement in the function? I get compiling error.

quote:
Originally posted by dinakar

Put both the inserts in the same stored proc one after another (in the appropriate order). No need for a trigger.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pootle_flump

1064 Posts

Posted - 2007-06-26 : 03:37:59

Try posting your code.

HINT: you don't want to do this in a function.

hunkieGuy
Starting Member

5 Posts

Posted - 2007-06-26 : 04:06:03

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

hunkieGuy
Starting Member

5 Posts

Posted - 2007-06-26 : 04:42:09

Only look at CREATE PROCEDURE anotherBuy.

KE

pootle_flump

1064 Posts

Posted - 2007-06-26 : 07:47:15

quote:
Originally posted by hunkieGuy

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?

Check out SCOPE_IDENTITY in Books OnLine.

hunkieGuy
Starting Member

5 Posts

Posted - 2007-06-27 : 02:40:10

Thank you Pootle flump. I have tried that, and it works fine.

KE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources