SQL Server: After Insert trigger Usage | How to populate related tables automatically?

Step1 – Create table tabPersonPrimary

create table tabPersonPrimary

(

EmpNo int primary key identity(1000,1),

[Name] char(20) not null,

Gender char(10) not null,

MaritalStatus char(10) not null,

City char(20),

Phone varchar(15)

)

Step2 – Create table tabUser

create table tabUser

(

EmpNo int foreign key references tabPersonPrimary(EmpNo),

LoginName char(15) not null,

LoginPassword char(15) not null

)

Step3 – Create a trigger PersonPrimary_AfterInsert

CREATE TRIGGER PersonPrimary_AfterInsert ON tabPersonPrimary

AFTER INSERT

AS

BEGIN

       SET NOCOUNT ON;

       DECLARE @EmpNo INT

       SELECT @EmpNo = inserted.EmpNo FROM inserted

       INSERT INTO tabUser VALUES(@EmpNo,CONCAT('user',@EmpNo),CONCAT('pass',@EmpNo))

END

Step 4 – Insert any number of records into tabPersonPrimary table, 

             tabUser will be populated automatically

insert into tabPersonPrimary 

values('Sonika','Female','Unmarried','Andra Pradesh','+919842502235')

Step 5 – Check both tables

select * from tabPersonPrimary

1000  Sonika   Female  Unmarried    Andra Pradesh   +919842502235

select * from tabUser

1000   user1000   pass1000