alter table t
add constraint 约束名 foreign key(id) references t1(id) on delete cascade

alter table t
add constraint 约束名 foreign key(id) references t1(id) on update cascade

create table t1
(id int,    档案编号  varchar(10)primary key ,   人数 int  )
insert into t1
select   1 ,      '10001',         2 union all
select   2 ,      '10002',         4 union all
select   3 ,      '10005',         3 

go

create table t2 
(id int,     档案编号 varchar(10) ,      成员编号 varchar(20))
insert into t2
select 1,       '10001',           '10001-001' union all
select 2,       '10001',           '10001-002' union all
select 3,       '10002',           '10002-001' union all
select 4 ,      '10002',           '10002-002' union all
select 5,       '10002',           '10002-003' union all
select 6,       '10002',           '10002-004' union all
select 7,       '10005',           '10005-001' union all
select 8,       '10005',           '10005-002' union all
select 9,       '10005',           '10005-003' 

--drop table t1
alter table t2
add constraint cons_update foreign key(档案编号) references t1(档案编号) on update cascade

alter table t2
add constraint cons_delete foreign key(档案编号) references t1(档案编号) on delete cascade


--如:将a表中的10005该成10003,b表中的第7.8.9行的数据应改变为 

update t1
set 档案编号='10003'
where 档案编号='10005'

select * from t2

/*
id          档案编号       成员编号                 
----------- ---------- -------------------- 
1           10001      10001-001
2           10001      10001-002
3           10002      10002-001
4           10002      10002-002
5           10002      10002-003
6           10002      10002-004
7           10003      10005-001
8           10003      10005-002
9           10003      10005-003

（所影响的行数为 9 行）
*/


delete t1
where 档案编号='10001'

select * from t2

/*
id          档案编号       成员编号                 
----------- ---------- -------------------- 
3           10002      10002-001
4           10002      10002-002
5           10002      10002-003
6           10002      10002-004
7           10003      10005-001
8           10003      10005-002
9           10003      10005-003

（所影响的行数为 7 行）
*/

create TRIGGER [dbo].[aUpdate] on [dbo].[a]
for update 
as
begin
    if update(a.'档案编号')
    update b
    set b.'档案编号'=a.'档案编号',
        b.'成员编号'=dbo.hanshu(a.'档案编号')--编号函数你有吧！
    From a,Deleted d ,Inserted i --Deleted和Inserted临时表
    where b.'档案编号'=d.'档案编号'
end

create trigger [dbo].[aDelete] on [dbo].[a]
for delete
as
begin
    if delete(a.id)
    delete b
    From a,Deleted d ,Inserted i --Deleted和Inserted临时表
    where b.'档案编号'=d.'档案编号'
end

alter table t2
add constraint cons_update foreign key(档案编号) references t1(档案编号) on update cascade

alter table t2
add constraint cons_delete foreign key(档案编号) references t1(档案编号) on delete cascade

create trigger [dbo].[bAdd] on [dbo].[b]
for update ,delete,insert
as
begin
    if update(b.'档案编号')
        begin
            update a
            set a.'人数'=a.'人数'+1
            where a.'档案编号'=b.'档案编号'
            
            update a
            set a.'人数'=a.'人数'-1
            From a,Deleted d ,Inserted i --Deleted和Inserted临时表
            where a.'档案编号'=d.'档案编号'
        end
   if insert(b.id)
        begin 
            update a
            set a.'人数'=a.'人数'+1
            where a.'档案编号'=b.'档案编号'
            
            insert into b
            ( '成员编号' nvarchar(20)
             )
            VALUES
            (
                dbo.hanshu(b.'档案编号')---你的成员编号生成函数
            )
        end
    if delete(b.id)
        begin
            update a
            set a.'人数'=a.'人数'-1
            where a.'档案编号'=b.'档案编号'
        end
    
end

--创建触发器

create trigger t1_update on t1
after update
as
begin
  update t2
          set 成员编号=replace(成员编号,left(成员编号,5),档案编号)
              where 档案编号 in (select 档案编号 from inserted)
end

select * from t2

update t1
set 档案编号='10008'
where 档案编号='10002'

/*
id          档案编号       成员编号                 
----------- ---------- -------------------- 
3           10008      10008-001
4           10008      10008-002
5           10008      10008-003
6           10008      10008-004
7           10003      10003-001
8           10003      10003-002
9           10003      10003-003

（所影响的行数为 7 行）
*/

ALTER function [dbo].[AutoPerson_Code](@Family_ID int)
returns varchar(30)
as
begin
declare @Family_Code nvarchar(30)
declare @str varchar(30)

select @Family_Code = Family_Code from FamilyInfo where Family_ID=@Family_ID --获取家庭编码

select @str=isnull(max(PersonCode),
right('00000000000000000'+right(@Family_Code,17),17)+'00')
from PersonInfo
where Family_ID=@Family_ID
set @str=left(@str,17) + right('00'+convert(varchar(2),convert(int,right(@str,2))+1),2)
return @str
end

]--试试
create table a(id int,档案编号 varchar(20),人数 int)
insert a select    1,  '10001',    2 
union all select   2,  '10002',    4 
union all select   3,  '10005',    3 

create table b(id int,档案编号 varchar(20),  成员编号 varchar(20))
insert b select  1,  '10001', '10001-001' 
union all select 2,  '10001', '10001-002' 
union all select 3,  '10002', '10002-001' 
union all select 4,  '10002', '10002-002' 
union all select 5,  '10002', '10002-003' 
union all select 6,  '10002', '10002-004' 
union all select 7,  '10005', '10005-001' 
union all select 8,  '10005', '10005-002' 
union all select 9,  '10005', '10005-003' 


sp_configure 'nested triggers',0

create trigger triaa on a 
for update
as
if update(档案编号)
begin
    update b set b.档案编号=c.档案编号,成员编号=replace(成员编号,b.档案编号,c.档案编号) from deleted a,inserted c where a.id=c.id and a.档案编号=b.档案编号
    update a set 人数=(select count(1) from b where b.档案编号=a.档案编号)
end
go
create trigger triab on a 
for delete
as
delete from b where 档案编号 in(select 档案编号 from deleted)
go
create   trigger triba on b
for update
as
if update(档案编号)
begin
    update b set b.成员编号=replace(b.成员编号,a.档案编号,c.档案编号) from deleted a,inserted c where a.id=c.id and b.id=a.id
    update a set 人数=(select count(1) from b where b.档案编号=a.档案编号)
end
go
create trigger tribb on b
for insert
as
    if exists(select 1 from a,b where a.档案编号=b.档案编号)
        update a set 人数