April 26, 2012 at 4:49 pm
Hello,
I have one parent & child table and i have make relation ship using a foreign key. Primary key in parent table is forein key in child table.
So want when i enter records in primary table, the id of each records automatically goes in to child table.
how can i do this?
waiting your reply....
Regards,
Gopal Singh
April 26, 2012 at 5:23 pm
Gopal Singh (4/26/2012)
Hello,I have one parent & child table and i have make relation ship using a foreign key. Primary key in parent table is forein key in child table.
So want when i enter records in primary table, the id of each records automatically goes in to child table.
how can i do this?
waiting your reply....
Regards,
Gopal Singh
What is inserting the data into the Parent table? If a stored procedure, you can add the additional insert into the Child there.
If it is a direct table insert into the Parent then you can achieve the desired result by implementing an AFTER INSERT TRIGGER on the Parent table that will insert rows into the Child after rows are inserted into the Parent.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 5:08 am
I'm not so sure I'd use a trigger. Those things are notorious performance and maintenance headaches.
What about using the OUTPUT clause. You can output the IDs generated from an INSERT statement into a temp table or table variable and then use those values for the INSERT into the child table. That's the method I'll use when I need to do something like this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2012 at 7:10 am
Grant Fritchey (4/27/2012)
I'm not so sure I'd use a trigger. Those things are notorious performance and maintenance headaches.What about using the OUTPUT clause. You can output the IDs generated from an INSERT statement into a temp table or table variable and then use those values for the INSERT into the child table. That's the method I'll use when I need to do something like this.
I agree with Grant here, if you have control over the process doing the insert, such as within a stored procedure.
A trigger, my second thought, would be your fallback if you do not have control over the process doing the insert, such as when someone has direct access to the tables and can issue inserts to the parent and may not follow a documented procedure to also populate the child table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply