好像没有用游标的必要:CREATE TABLE test(id int not null,档案编号 varchar(30),成员编号 varchar(30))
insert into test values(1,'1','1-001')
insert into test values(2,'1','1-002')
insert into test values(3,'1','1-003')
insert into test values(4,'1','1-004')
insert into test values(5,'1','1-005')
insert into test values(6,'1','1-006')
go
create trigger 删除
on test
for delete
as
begin
update t
set
成员编号=t.档案编号+'-'+right('000'+rtrim((select count(*) from test where 档案编号=t.档案编号 and 成员编号<=t.成员编号)),3)
from
test t
where
exists(select 1 from deleted where 档案编号=t.档案编号 and 成员编号<t.成员编号)
end
godelete test where 成员编号='1-002'
select * from test
/*
id 档案编号 成员编号
----------- ------------------------------ ------------------------------
1 1 1-001
3 1 1-002
4 1 1-003
5 1 1-004
6 1 1-005
*/
godrop trigger 删除
drop table test
go
insert into test values(1,'1','1-001')
insert into test values(2,'1','1-002')
insert into test values(3,'1','1-003')
insert into test values(4,'1','1-004')
insert into test values(5,'1','1-005')
insert into test values(6,'1','1-006')
go
create trigger 删除
on test
for delete
as
begin
update t
set
成员编号=t.档案编号+'-'+right('000'+rtrim((select count(*) from test where 档案编号=t.档案编号 and 成员编号<=t.成员编号)),3)
from
test t
where
exists(select 1 from deleted where 档案编号=t.档案编号 and 成员编号<t.成员编号)
end
godelete test where 成员编号='1-002'
select * from test
/*
id 档案编号 成员编号
----------- ------------------------------ ------------------------------
1 1 1-001
3 1 1-002
4 1 1-003
5 1 1-004
6 1 1-005
*/
godrop trigger 删除
drop table test
go
create trigger 删除
on 成员表
for delete
as
declare @k varchar(30)
declare @q varchar(30)
declare @m varchar(30)
select @m=成员编号 from deleted
set @m=right(@m,3)
--select cast(@m as int)
declare del cursor for Select 成员编号,档案编号 from deleted
open del
fetch next from del into @k,@q
while @@fetch_status = 0
begin
set @k=right(@k,3)
-- select cast(@k as int)
if(cast(@k as int)> cast(@m as int) )
begin
update 成员表 set 成员编号=(@k-1) where 成员编号=@k and 档案编号=@q
end
fetch next from del into @k,@q
end
close del
deallocate del
CREATE TABLE t
(
id int not null,
档案编号 varchar(30),
成员编号 varchar(30)
)
insert into tselect 1 ,'1', '1-001' union all
select 2, '1', '1-002' union all
select 3, '1', '1-003' union all
select 4, '1', '1-004' union all
select 5, '1', '1-005' union all
select 6, '1' ,'1-006' create trigger tri_t on t
after delete
as
begin
update t
set 成员编号='1-'+right('000'+ ltrim(cast(right(成员编号,3)as int)-1),3)
where 成员编号>all(select 成员编号 from deleted)
end--删除
delete t
where 成员编号='1-002'--显示
select * from t/*
id 档案编号 成员编号
----------- ------------------------------ ------------------------------
1 1 1-001
3 1 1-002
4 1 1-003
5 1 1-004
6 1 1-005(所影响的行数为 5 行)
*/
(
id int not null,
档案编号 varchar(30),
成员编号 varchar(30)
) insert 成员表 select 1, '1', '1-001'
union all select 2, '1', '1-002'
union all select 3, '1', '1-003'
union all select 4, '1', '1-004'
union all select 5, '1', '1-005'
union all select 6, '1', '1-006' --删除成员编号为1-002的后,后面003,004,005,006依次-1变为002,003,004,005 create trigger 删除 on 成员表
for delete
as
begin
update 成员表 T
set 成员编号=档案编号+'-'+right('000'+rtrim((select count(1) from 成员表 where 档案编号=T.档案编号 and 成员编号<=T.成员编号)),3)
from T
where exists(select 1 from deleted where 档案编号=T.档案编号 and 成员编号<T.成员编号)
end
insert into 成员表 values(1, '1', '1-001')
insert into 成员表 values(2, '1', '1-002')
insert into 成员表 values(3, '1', '1-003')
insert into 成员表 values(4, '1', '1-004')
insert into 成员表 values(5, '1', '1-005')
insert into 成员表 values(6, '1', '1-006')
godeclare @成员编号 as varchar(30)
set @成员编号 = '1-002'delete from 成员表 where 成员编号 = @成员编号update 成员表
set 成员编号 = left(成员编号,charindex('-',成员编号)) + right('000'+cast(cast(substring(成员编号,charindex('-',成员编号) + 1 , len(成员编号)) as int) - 1 as varchar),3)
where 成员编号 > @成员编号select * from 成员表drop table 成员表/*
id 档案编号 成员编号
----------- ------------------------------ ------------------------------
1 1 1-001
3 1 1-002
4 1 1-003
5 1 1-004
6 1 1-005(所影响的行数为 5 行)
*/
t 是那个表的 ?
t 是那个表的 ?
--------------------
我自己建的,用中文名不方便