表a
id 档案编号 人数
1 10001 2
2 10002 4
3 10005 3表b
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 10005 10005-001
8 10005 10005-002
9 10005 10005-003
b表中的成员编号为按照一定格式自动生成.
表a中的档案编号改变时候,b表中的档案编号要跟着改变,同时b表中的成员编号也要改变.
如:将a表中的10005该成10003,b表中的第7.8.9行的数据应改变为
7 10003 10003-001
8 10003 10003-002
9 10003 10003-003还有,改变b表中的档案编号的时候,a表中的'人数',和b表中的成员编号也应该相应改变
如:将b表中的第4行的档案编号改为10001,则a表和b表分别变化为
id 档案编号 人数
1 10001 3
2 10002 3
3 10005 3id 档案编号 成员编号
1 10001 10001-001
2 10001 10001-002
3 10002 10002-001
4 10001 10001-003
5 10002 10002-002
6 10002 10002-003
7 10005 10005-001
8 10005 10005-002
9 10005 10005-003
删除a表中的一行记录b表中对应的档案编号的记录也要全删除掉
在b表中填加一条记录10005,a表中10005记录的人数应该增加1,b表中填加的记录格式应该为
10 10005 10005-004
id 档案编号 人数
1 10001 2
2 10002 4
3 10005 3表b
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 10005 10005-001
8 10005 10005-002
9 10005 10005-003
b表中的成员编号为按照一定格式自动生成.
表a中的档案编号改变时候,b表中的档案编号要跟着改变,同时b表中的成员编号也要改变.
如:将a表中的10005该成10003,b表中的第7.8.9行的数据应改变为
7 10003 10003-001
8 10003 10003-002
9 10003 10003-003还有,改变b表中的档案编号的时候,a表中的'人数',和b表中的成员编号也应该相应改变
如:将b表中的第4行的档案编号改为10001,则a表和b表分别变化为
id 档案编号 人数
1 10001 3
2 10002 3
3 10005 3id 档案编号 成员编号
1 10001 10001-001
2 10001 10001-002
3 10002 10002-001
4 10001 10001-003
5 10002 10002-002
6 10002 10002-003
7 10005 10005-001
8 10005 10005-002
9 10005 10005-003
删除a表中的一行记录b表中对应的档案编号的记录也要全删除掉
在b表中填加一条记录10005,a表中10005记录的人数应该增加1,b表中填加的记录格式应该为
10 10005 10005-004
alter table t
add constraint 约束名 foreign key(id) references t1(id) on delete cascadealter table t
add constraint 约束名 foreign key(id) references t1(id) on update cascade
其实我要的就是因为建立了关联,表a修改一条后,表b一次更新了多条记录,表b中的哪个成员编号相应改变的触发器,
我写的这个是2表建立了级联后的
create trigger d
on b
for delete,update,insert
as
begin
update t
set
成员编号=t.档案编号+'-'+right('000'+rtrim((select count(*) from b where 档案编号=t.档案编号 and 成员编号<=t.成员编号)),3)
from
b t
where
exists(select 1 from deleted where 档案编号=t.档案编号 and 成员编号<t.成员编号)
end但是改了a表后,b表的成员编号还是没变
(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 gocreate 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 cascadealter 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.'档案编号'
endcreate 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
--改变b表的正在写
你说呢alter table t2
add constraint cons_update foreign key(档案编号) references t1(档案编号) on update cascadealter table t2
add constraint cons_delete foreign key(档案编号) references t1(档案编号) on delete cascade
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)
endselect * from t2update 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',0create 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 人数=(select count(1) from b where b.档案编号=a.档案编号)sp_configure 'nested triggers',1
(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 gocreate 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' --建约束
alter table t2
add constraint cons_update foreign key(档案编号) references t1(档案编号) on update cascade
go
alter table t2
add constraint cons_delete foreign key(档案编号) references t1(档案编号) on delete cascade
--创建触发器create trigger t1_update on t1
after update
as
begin
update t2
set 成员编号=replace(成员编号,left(成员编号,5),档案编号)
where 档案编号 in (select 档案编号 from inserted)
end--如:将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 10003-001
8 10003 10003-002
9 10003 10003-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 10003-001
8 10003 10003-002
9 10003 10003-003(所影响的行数为 7 行)
*/
update t1
set 档案编号='10008'
where 档案编号='10002'select * from t2/*
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 行)
*/
--drop table t2--alter table t2
--drop constraint cons_delete
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 gocreate 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' --建约束
alter table t2
add constraint cons_update foreign key(档案编号) references t1(档案编号) on update cascade
go
alter table t2
add constraint cons_delete foreign key(档案编号) references t1(档案编号) on delete cascade
--创建触发器create trigger t1_update on t1
after update
as
begin
update t2
set 成员编号=replace(成员编号,left(成员编号,5),档案编号)
where 档案编号 in (select 档案编号 from inserted)
endcreate trigger add_t1 on t2
after insert
as
begin
update t1
set 人数=b.人数
from t1 a
join( select 档案编号,count(*)as '人数' from t2 group by 档案编号 )b
on a.档案编号=b.档案编号
where a.档案编号 in (select 档案编号 from inserted)
end
--如:将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 10003-001
8 10003 10003-002
9 10003 10003-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 10003-001
8 10003 10003-002
9 10003 10003-003(所影响的行数为 7 行)*/
update t1
set 档案编号='10008'
where 档案编号='10002'select * from t2/*
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 行)
*/
--当向t2表中插入新的记录时,t1表人数自动增加insert into t2
select 11,'10008', '10008-005'
select * from t1
/*
id 档案编号 人数
----------- ---------- -----------
3 10003 3
2 10008 5(所影响的行数为 2 行)
*/
--drop table t1--alter table t2
--drop constraint cons_update
on b
for delete,update,insert
asIF EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM INSERTED)
update a set 人数 =(select count(*) from b where 档案编号 in (select 档案编号 from deleted )) where 档案编号 in (select 档案编号 from deleted )
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
begin
update a set 人数 =(select count(*) from b where 档案编号=(select 档案编号 from inserted)) where 档案编号=(select 档案编号 from inserted)
end
IF update(档案编号)
begin
update t
set
成员编号=d.档案编号+'-'+right('000'+rtrim((select count(*) from b where 档案编号=d.档案编号 and 成员编号<=t.成员编号)),3)
from
b t, inserted d
where
exists(select 1 from deleted where 档案编号=d.档案编号 and 成员编号<t.成员编号)
end
create table test_b(id int identity(1,1),data_no varchar(20),m_no varchar(20))
insert into test_a(data_no,no)values('a0001',3)
insert into test_a(data_no,no)values('b0001',2)
insert into test_b(data_no,m_no)values('a0001','a0001-001')
insert into test_b(data_no,m_no)values('a0001','a0001-002')
insert into test_b(data_no,m_no)values('a0001','a0001-003')
insert into test_b(data_no,m_no)values('b0001','b0001-001')
insert into test_b(data_no,m_no)values('b0001','b0001-002')
CREATE TRIGGER [developer].[td_test_a] ON [developer].[test_a]
FOR DELETE
AS
BEGIN
delete test_b from deleted join test_b on test_b.data_no = deleted.data_no
END
GOCREATE TRIGGER [developer].[ut_update] ON [developer].[test_a]
FOR UPDATE
AS
if update(data_no)
begin
UPDATE TEST_B SET DATA_NO=INSERTED.DATA_NO,M_NO=INSERTED.DATa_NO+
substring(TEST_B.M_NO,charindex('-',TEST_B.M_NO,1),len(TEST_B.M_NO))
from inserted join deleted on deleted.id=inserted.id
join test_b on deleted.data_no=test_b.data_no
end
GOCREATE TRIGGER [developer].[ut_test_b] ON [developer].[test_b]
FOR UPDATE
AS
BEGIN
if update(data_no) and update(m_no)
begin
return
end
else
begin
update test_b set m_no=INSERTED.DATa_NO+
substring(TEST_B.M_NO,charindex('-',TEST_B.M_NO,1),len(TEST_B.M_NO))
from inserted join deleted on inserted.id=deleted.id join
test_b ON deleted.id=test_b.id
if exists( select * from inserted join test_a on inserted.data_no=test_a.data_no)
begin
update test_a set no=no+(select count(*) from inserted where inserted.data_no=test_a.data_no)
from inserted join deleted on inserted.id=deleted.id join test_a ON inserted.data_no=test_a.data_no
end
if exists( select * from deleted join test_a on deleted.data_no=test_a.data_no)
begin
update test_a set no=no-(select count(*) from deleted where deleted.data_no=test_a.data_no)
from inserted join deleted on inserted.id=deleted.id join test_a ON deleted.data_no=test_a.data_no end
endEND
GO
应该是这个.......................
create trigger 家庭成员
on b
for delete,update,insert
asIF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
begin
update a set 人数 =(select count(*) from b where 档案编号=(select 档案编号 from inserted)) where 档案编号=(select 档案编号 from inserted)
end
IF update(档案编号)
begin
update t
set
成员编号=b.档案编号+'-'+right('000'+rtrim((select count(*) from t where 档案编号=d.档案编号 and 成员编号<=t.成员编号)),3)
from
b t, inserted d
where
exists(select 1 from deleted where 档案编号=d.档案编号 and 成员编号<t.成员编号)
end
IF EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM INSERTED)
begin
update a set 人数 =(select count(*) from b where 档案编号 in (select 档案编号 from deleted )) where 档案编号 in (select 档案编号 from deleted )
update t
set
成员编号=t.档案编号+'-'+right('000'+rtrim((select count(*) from t where 档案编号=t.档案编号 and 家庭成员编号<=t.家庭成员编号)),3)
from
b t
where
exists(select 1 from deleted where 档案编号=t.档案编号 and 成员编号<t.成员编号)
end
go
create table t1
(id int identity(1,1), 档案编号 varchar(10)primary key , 人数 int )
insert into t1
select '10001', 2 union all
select '10002', 4 union all
select '10005', 3 gocreate table t2
(id int identity(1,1), 档案编号 varchar(10) , 成员编号 varchar(20))
insert into t2
select '10001', '10001-001' union all
select '10001', '10001-002' union all
select '10002', '10002-001' union all
select '10002', '10002-002' union all
select '10002', '10002-003' union all
select '10002', '10002-004' union all
select '10005', '10005-001' union all
select '10005', '10005-002' union all
select '10005', '10005-003'
create trigger tgt1 on t1 for insert,update,delete
as
declare @i int
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
begin declare @num int
select @num=人数 from inserted
set @i=1
while(@i<=@num)
begin
insert into t2 select 档案编号,档案编号+'-'+right('000'+cast(@i as varchar),3) from inserted
set @i=@i+1
end
end
IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
begin
delete t2 from t2 a,deleted b where a.档案编号=b.档案编号
endIF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
begin
if(update(档案编号))
begin
update t2 set 档案编号=a.档案编号,成员编号=replace(成员编号,b.档案编号,a.档案编号) from t2 b,inserted a,deleted c where c.档案编号=b.档案编号
end
else if(update(人数))
begin
declare @num1 int
declare @num2 int
select @num1=人数 from inserted
select @num2=count(1) from t2 where 档案编号=(select 档案编号 from inserted)
select @num1
select @num2
set @i=1
while(@i<=@num1-@num2)
begin
insert into t2 select b.档案编号,b.档案编号+'-'+right('000'+cast(max(substring(b.成员编号,charindex('-',b.成员编号)+1,len(b.成员编号)))+1 as varchar),3) from inserted a,t2 b where a.档案编号=b.档案编号 group by b.档案编号
set @i=@i+1
end
set @i=1
while(@i<=@num2-@num1)
begin
delete t2 from t2 a,(select 档案编号+'-'+right('000'+cast(max(substring(成员编号,charindex('-',成员编号),len(成员编号))) as varchar),3) c
from t2 where 档案编号=(select 档案编号 from inserted) group by 档案编号) b where a.成员编号=b.c
set @i=@i+1
end
end
endselect * from t1
select * from t2--测试:
insert into t1 select '10006',2update t1 set 档案编号='10004' where 档案编号='10006'update t1 set 人数=10 where id=3delete t1 where 档案编号='10004'--删除表
drop table t2
drop table t1