copy an identity column into another table

Question

I have 2 tables that are related,both have identity columns for primary keys and i am using a vb form to insert data into them,My problem is that i cannot get the child table to get the primary key of the parent table and use this as its foreign key in my database. the data is inserted fine though no foreign key constraint is made.I am wondering if a trigger will do it and if so how. All my inserting of data is done in vb.

The user wont insert any keys. all these are identity columns that are auto generated. If a trigger is my way out please illustrate with an example.

If there is another way i can do this in VB itself then please advise and an example will be greatly appreciated

Thanks in advance

Answer 1

Two ways came to my mind (will assume SQL Server):

1. Insert into the first table (parent) and then get the identity key generated with @@IDENTITY then insert the record in the child table with the retrieved value.
2. Write a stored procedure that inserts both records and uses the @@IDENTITY internally.

Answer 2

Hi TekBlues I really appreciate the suggestions but the truth is i dont know know how to do it. I am currently reading up on @@identity and just do not understand how it works. Hav you written any simple statements of the sort? If so can i please have a look at them. Some i see online are way to advanced for the likes of me at this stage.

Hope you can help..

Thanks in advance

Answer 3

I would check your database-design: "My problem is that i cannot get the child table to get the primary key of the parent table" On the Child-table you can set a identity-column but not as foreignkey. Please create a new column of datatype int as foreignkey.

create table parent (id int identity(1,1) not null, data varchar(100)...)
create table child (id int identity(1,1) not null, parent_id int not null, data varchar(100)...)

To retrieve the Information stored in your database, use a query like:

select parent.id, parent.data, child.data
from parent, child 
where parent.id = child.parent_id

To insert data into both tables, you have to use two steps:

insert parent(data) values('This is a test for parent-record')
insert child(parent_id, data) values(@@IDENTITY, 'This is testdata for the child record')

Be carefull with the @@IDENTITY Value because if there are triggers fireing at insert behind the parent table you will get probably not the last parent-table identity-value dependig on the work the trigger does.