excellent job providing the sample DDL and data;

it made it very easy to change.

check out the changes i made to your trigger, i think it does exactly what you wanted:

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

GO

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 = tb1_parent.tb1_parent_id

FROM Inserted

INNER JOIN tb1_parent

ON tb1_parent.tb1_parent_bus_key = Inserted.tb2_child_bus_key

where 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