ok, thanks for the tips on posting.

Here's some code that I hope will help with my problem.

Also, I'm not understanding your comment on multiple inserts -- I want all child records to have the same parent key.

CREATE TABLE [dbo].[tb1_parent](

[tb1_parent_id] [int] IDENTITY(1001,1) NOT NULL,

[tb1_parent_bus_key] nvarchar(10))

CREATE TABLE [dbo].[tb2_child](

[tb2_child_id] [int] IDENTITY(2001,1) NOT NULL,

[tb2_parent_id] [int],

[tb2_child_bus_key] nvarchar(10))

BEGIN TRANSACTION

insert into tb1_parent (tb1_parent_bus_key) values ('A0001')

insert into tb2_child (tb2_child_bus_key) values ('A0001')

COMMIT

select * from tb1_parent

select * from tb2_child

-- note the null parent_id in the child record

create TRIGGER [dbo].[trg_tb1_parent_Insert]

ON [dbo].[tb2_child]

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

UPDATE tb2_child

SET tb2_parent_id =

(select tb1_parent_id from tb1_parent

where tb1_parent.tb1_parent_bus_key = tb2_child.tb2_child_bus_key)

FROM tb2_child

INNER JOIN Inserted ON Inserted.tb2_child_id = tb2_child.tb2_child_id

END

BEGIN TRANSACTION

insert into tb1_parent (tb1_parent_bus_key) values ('A0002')

insert into tb2_child (tb2_child_bus_key) values ('A0002')

COMMIT

select * from tb1_parent

select * from tb2_child

-- this is the result that I want, I just thought I might avoid the select statement

BEGIN TRANSACTION

insert into tb1_parent (tb1_parent_bus_key) values ('A0003')

insert into tb2_child (tb2_child_bus_key) values ('A0003')

insert into tb2_child (tb2_child_bus_key) values ('A0003')

COMMIT

select * from tb1_parent

select * from tb2_child

-- this is also the result on want, on multiple inserts to the child record