触发器的问题？这个怎么写？

Question

表a
id    档案编号   
1     10001
2     10002
3     10003
3     10004
表b
id    档案编号  成员编号
1     10001    10001-0001
2     10001    10001-0002
3     10001    10001-0003
4     10001    10001-0004
5     10002    10002-0001
6     10002    10002-0002
7     10002    10002-0003
8     10003    10003-0001
9     10004    10004-0001
10    10004    10004-0002
11    10004    10004-0003
a表和b表的档案编号做了级联修改和删除
现在求一个b表的触发器 实现以下功能：
1).直接在b表中删除一条记录的时候,该档案编号的成员编号要跟着相应改变
如：删除b表中id为2的行 ,那么b表数据则改变为
id    档案编号  成员编号
1     10001    10001-0001
3     10001    10001-0002
4     10001    10001-0003
5     10002    10002-0001
6     10002    10002-0002
7     10002    10002-0003
8     10003    10003-0001
9     10004    10004-0001
10    10004    10004-0002
11    10004    10004-0003
2).修改b表中的档案编号，成员编号也要相应改变
如：修改b表中id为3的行的档案编号为10002，那么b表数据则改变为
id    档案编号  成员编号
1     10001    10001-0001
3     10002    10002-0004
4     10001    10001-0002
5     10002    10002-0001
6     10002    10002-0002
7     10002    10002-0003
8     10003    10003-0001
9     10004    10004-0001
10    10004    10004-0002
11    10004    10004-0003
3）.修改a表中的档案编号,b表也要跟着相应改变
如：修改a 表中的档案编号10004为10005，则b表改变为
id    档案编号  成员编号
1     10001    10001-0001
3     10002    10002-0004
4     10001    10001-0002
5     10002    10002-0001
6     10002    10002-0002
7     10002    10002-0003
8     10003    10003-0001
9     10005    10005-0001
10    10005    10005-0002
11    10005    10005-0003注意：a表和b表已经做了级联修改和删除。要实现上面的功能，这个触发器怎么写的？
我自己写了个。。不能实现第3个功能。
该了a表后b表是多条记录发生了改变.
create trigger 家庭成员
on b
for delete,update,insert
as
IF EXISTS(SELECT 1 FROM deleted)
begin
 IF EXISTS(SELECT 1 FROM deleted) and EXISTS(SELECT 1 FROM inserted)
begin
update b
  set
      成员编号=(select 档案编号 from inserted where id=t.id)+'-'+right('000'+rtrim((select count(*) from b where 档案编号 in (select 档案编号 from inserted ))),4)
  from
      b
  where id in (select id from deleted)
end
if exists(select * from b where 档案编号 in (SELECT 档案编号 FROM deleted))
begin
update b
  set
      成员编号=t.档案编号+'-'+right('000'+rtrim((select count(*) from b where 档案编号=b.档案编号 and 家庭成员编号<=b.成员编号)),4)
  from
      b
  where
      exists(select 1 from deleted where 档案编号=b.档案编号 and 家庭成员编号<b.家庭成员编号)
end
end
go
 

Answer 1

我现在是3层关系，，，a表和b表的档案编号关联,b表在和多个表的成员编号关联,你要是写在a表里面,我后面的表和b表之间的哪个不也要写在b表里面?

Answer 2

---try  
create   trigger   家庭成员 on   b 
for   delete,update,insert 
as 
begin  declare @num int set @num=1
IF   EXISTS(SELECT 1 FROM   deleted) and EXISTS(SELECT 1 FROM inserted)  ---更新的情况
begin 

update b 
    set 成员编号=t.档案编号+'-'+right((select max(成员编号)+@num from b where id=t.id),4),@num=@num+1
    from b inner join inserted t on b.id=tid
    
end 

IF EXISTS(SELECT 1 FROM   deleted) and NOT EXISTS(SELECT 1 FROM inserted)  ---删除的情况
begin 

update b 
    set 成员编号=t.档案编号+'-'+right((select max(成员编号)+@num from b where id=t.id),4),@num=@num+1
    from b inner join deleted t on b.id=tid
    
end 
end 
go 

Answer 3

--上面的语句改改 ,  用这个试试
create table tb(id  int, 档案编号 varchar(10),    成员编号 varchar(10))
insert tb
select 1,'10001','10001-0001' 
union select 2,'10001','10001-0002' 
union select 3,'10001','10001-0003' 
union select 4,'10001','10001-0004' 
union select 5,'10002','10002-0001' 
union select 6,'10002','10002-0002' 
union select 7,'10002','10002-0003' 
union select 8,'10003','10003-0001' 
union select 9,'10004','10004-0001' 
union select 10,'10004','10004-0002' 
union select 11,'10004','10004-0003' 
go
create  trigger   家庭成员 on   tb 
for delete,update
as 
begin  declare @num int,@id int
IF   EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)  ---更新的情况
begin 
set @num=1
update b 
    set 成员编号=t.档案编号+'-'+right('0000'+rtrim((select right(max(成员编号),4)+@num from tb where id=t.id)),4),@num=@num+1
    from tb b inner join inserted t on b.id=t.id
    
end 

  IF EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted)  ---删除的情况
  begin 
select @num=count(1),@id=1 from tb b inner join deleted t on b.档案编号=t.档案编号 AND b.成员编号>t.成员编号

while(@num>0)
begin 
update b 
    set 成员编号=case when t.成员编号<b.成员编号 
then t.档案编号+'-'+right('0000'+rtrim((select right(count(成员编号),4)+b.id-t.id from tb where 档案编号=t.档案编号 and 成员编号<t.成员编号)),4)
else b.成员编号 end
from tb b inner join deleted t on b.档案编号=t.档案编号
where b.id=t.id+@id
set @id=@id+1
set @num=@num-1
end  end 
end go
delete from tb where id in (3,2)   ---  删除的语句
go
/*  更新的语句
update tb
set 档案编号='10002'
where id in (3,4)
*/
go 
select * from tbdrop table tb

Answer 4

第一个触发器. 可能有手误. 第二个饭后再写.
CREATE TRIGGER tb_ONDelete
ON tb
FOR DELETE
AS
DECLARE @cid VARCHAR(10),@id VARCHAR(20)
UPDATE a SET a.成员编号= a.档案编号 + '-' + 
RIGHT(
'000000000' + RTRIM(CAST(SUTFF(a.成员编号,1,LEN(a.档案编号)+1,'') AS INT)-1),
LEN(SUTFF(a.成员编号,1,LEN(a.档案编号)+1,'')
)
FROM tb a
INNER JOIN DELETED b
ON a.档案编号=b.档案编号 AND a.成员编号>b.成员编号 --若档案编号的位数为5,成员编号的后辍位数为5,确定，且不变更，那么语句可以简化为
/*
DECLARE @cid VARCHAR(10),@id VARCHAR(20)
UPDATE a SET a.成员编号= a.档案编号 + '-' + 
RIGHT('000000000' + RIGHT(CAST(RIGHT(a.成员编号,5) AS INT)-1),5)
FROM tb a
INNER JOIN DELETED b
ON a.档案编号=b.档案编号 AND a.成员编号>b.成员编号
*/

Answer 5

--上面的语句改改   ,     用这个试试 
create   table   tb(id     int,   档案编号   varchar(10),         成员编号   varchar(10)) 
insert   tb 
select   1,'10001','10001-0001'   
union   select   2,'10001','10001-0002'   
union   select   3,'10001','10001-0003'   
union   select   4,'10001','10001-0004'   
union   select   5,'10002','10002-0001'   
union   select   6,'10002','10002-0002'   
union   select   7,'10002','10002-0003'   
union   select   8,'10003','10003-0001'   
union   select   9,'10004','10004-0001'   
union   select   10,'10004','10004-0002'   
union   select   11,'10004','10004-0003'   
go 
---创建触发器
create trigger 家庭成员 on tb   
for delete,update 
as   
begin   
declare @num int,@id int 

IF EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)     ---更新的情况 
begin   
set   @num=1 
update b   
set 成员编号=t.档案编号+'-'+right('0000'+rtrim((select right(max(成员编号),4)+@num from tb where id=t.id)),4),@num=@num+1 
from tb b inner join inserted t on b.id=t.id        
end   

IF   EXISTS(SELECT   1   FROM   deleted)   AND   NOT   EXISTS(SELECT   1   FROM   inserted)     ---删除的情况 
begin   
select   @num=count(1),@id=1   from   tb   b   inner   join   deleted   t   on   b.档案编号=t.档案编号   AND   b.成员编号> t.成员编号 

while(@num> 0) 
begin   
update   b   
set 成员编号=case when t.成员编号 <b.成员编号   
then   t.档案编号+'-'+right('0000'+rtrim((select   right(count(成员编号),4)+b.id-t.id   from   tb   where   档案编号=t.档案编号   and   成员编号 <t.成员编号)),4) 
else   b.成员编号   
    end 
from tb b inner join deleted t on b.档案编号=t.档案编号 
where b.id=t.id+@id 
set @id=@id+1 
set @num=@num-1 
end 

end   
end   go 
delete from tb where id in (3,2)       ---     删除的语句 
go 
/*     更新的语句 
update   tb 
set   档案编号='10002' 
where   id   in   (3,4) 
*/ 
go   
select * from tb drop table tb

Answer 6

leo_lesley 
还是不行啊。修改a表中的档案编号的时候就回报错了

Answer 7

这个问题我想了一下,还是稍有点复杂的.如果建两个触发器,一个insert,一个update
insert触发器会update成员编号,引起update触发器触发.
这样一来,insert处理的update就全乱了.写起来还是要慎重一点.如果建一个触发器,把insert和update都放在里面,默认触发器自调用是关闭的,不会在insert事件时再调用自身的update,可以解决这个问题.但个人觉得这种问题,最好是在前台程序里的业务逻辑里来控制,不要过份依赖数据库端的实现, 引起数据混乱,就不好了.

Answer 8

--上面的语句改改   ,     用这个试试 
create table ta(id int,档案编号 varchar(10)) 
insert   ta 
select   1,'10001'
union   select   2,'10002'
union   select   3,'10003'
union   select   4,'10004'
go
create   table   tb(id     int,   档案编号   varchar(10),         成员编号   varchar(10)) 
insert   tb 
select   1,'10001','10001-0001'   
union   select   2,'10001','10001-0002'   
union   select   3,'10001','10001-0003'   
union   select   4,'10001','10001-0004'   
union   select   5,'10002','10002-0001'   
union   select   6,'10002','10002-0002'   
union   select   7,'10002','10002-0003'   
union   select   8,'10003','10003-0001'   
union   select   9,'10004','10004-0001'   
union   select   10,'10004','10004-0002'   
union   select   11,'10004','10004-0003'   
go 
---创建触发器
create trigger 家庭成员 on tb   
for delete,update 
as   
begin   
    declare @num int,@id int 
    
    IF EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)     ---更新的情况 
    begin   
        set   @num=1 
        update b   
        set 成员编号=t.档案编号+'-'+right('0000'+rtrim((select right(max(成员编号),4)+@num from tb where id=t.id)),4),@num=@num+1 
        from tb b inner join inserted t on b.id=t.id        
    end   
    
    IF   EXISTS(SELECT   1   FROM   deleted)   AND   NOT   EXISTS(SELECT   1   FROM   inserted)     ---删除的情况 
    begin   
        select   @num=count(1),@id=1   from   tb   b   inner   join   deleted   t   on   b.档案编号=t.档案编号   AND   b.成员编号> t.成员编号 
        
        while(@num> 0) 
        begin   
            update   b   
            set 成员编号=case when t.成员编号 <b.成员编号   
                    then   t.档案编号+'-'+right('0000'+rtrim((select   right(count(成员编号),4)+b.id-t.id   from   tb   where   档案编号=t.档案编号   and   成员编号 <t.成员编号)),4) 
                    else   b.成员编号   
                    end 
            from tb b inner join deleted t on b.档案编号=t.档案编号 
            where b.id=t.id+@id 
            set @id=@id+1 
            set @num=@num-1 
        end 
    
    end   
end   
go
----在ta表上创建触发器
create trigger tra on ta   
for delete,update 
as   
begin   
    
    IF EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)     ---更新的情况 
    begin   
        update tb   
        set 档案编号=(select 档案编号 from inserted),成员编号=(select 档案编号 from inserted)+right(成员编号,5)
        from tb b inner join deleted t on b.档案编号=t.档案编号        
    end   
    
    IF   EXISTS(SELECT   1   FROM   deleted)   AND   NOT   EXISTS(SELECT   1   FROM   inserted)     ---删除的情况 
    begin   
        delete  from tb where 档案编号 in(select 档案编号 from deleted)
    end   
end   
gogo 
--delete from tb where id in (3,2)       ---     删除的语句 
go 
/*     更新的语句 
update   tb 
set   档案编号='10002' 
where   id   in   (3,4) 
*/ 
go   
update ta 
set 档案编号='10005'
where id=4
select * from tb 
select * from ta
drop table tb,ta