create function f_MaxStudyId(@Parterid int)
    returns int
as
begin
    declare @re int
    select @re=id from(
                select id=max(StudyId) from tb where Parterid=@Parterid
    ) a

    set @re=isnull(@re,0)+1
    return @re
end
go

create table tb(
ID int identity(1,1) ,
Name varchar(20),
Parterid int,
StudyId int
)
go

create trigger tri on tb
instead of insert
as
    declare @Parentid int,@id int
    select * into # from inserted order by Parterid
    update # set @id=case when Parterid=@Parentid then @id+1 else dbo.f_MaxStudyId(Parterid) end,
                  StudyId=@id,
                  @Parentid=Parterid
    insert into tb select Name,Parterid,StudyId from #
go

insert into tb([Name],Parterid) values('Unit1',100)
insert into tb([Name],Parterid) values('Unit2',100)
insert into tb([Name],Parterid) values('Unit3',101)
insert into tb([Name],Parterid) values('Unit4',101)
insert into tb([Name],Parterid) values('Unit5',101)
insert into tb([Name],Parterid) values('Unit5',101)

select * from tb

drop function f_MaxStudyId
drop table tb

/*
ID          Name                 Parterid    StudyId     
----------- -------------------- ----------- ----------- 
1           Unit1                100         1
2           Unit2                100         2
3           Unit3                101         1
4           Unit4                101         2
5           Unit5                101         3
6           Unit5                101         4

（所影响的行数为 6 行）

*/

------解决方案--------------------
邹老大的例子.

SQL code
--自动编号的例子.材料编号=类别编号+流水号

--创建自定义函数,得到新的ID
create function f_getid(
@类别编号 varchar(3))
returns int
as
begin
    declare @re int
    select @re=right(id,4) from(
        select id=max(材料编号) from tb where 类别编号=@类别编号
    ) a
    set @re=isnull(@re,0)+1
    return(@re)
end
go

--创建测试表
create table tb(材料编号 varchar(7) primary key default '',类别编号 varchar(3),材料名称 varchar(10))
go

--创建触发器,自动生成材料编号
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by 类别编号
declare @类别编号 varchar(3),@id int
update #t set @id=case when @类别编号=类别编号 then @id+1 else dbo.f_getid(类别编号) end
    ,材料编号=类别编号+right('0000'+cast(@id as varchar),4)
    ,@类别编号=类别编号
insert into tb select * from #t
go

--插入数据测试
insert into tb(类别编号,材料名称)
select '101','A材料'
union all select '101','B材料'
union all select '302','C材料'

--显示结果
select * from tb order by 材料编号

go
--删除测试环境
drop table tb
drop function f_getid