In this transaction UREG, both Insert queries executed. After the successfully execution of queries, transaction UREG is committed n then permanently save database changes. if (Table_loginInfo) insert query is not successful executed then CATCH block is executed here and ROLLBACK Command executed and remove all inserted row from each table.
begin tran UREG
declare @Uid int
insert into Table_UserInfo(name, email)
select @Uid=max(userid) from Table_UserInfo
insert into Table_loginInfo( userid, username, password)
commit tran UREG
rollback tran UREG