CREATE TRIGGER ruku_trigger6 ON ruku
FOR DELETE
AS
begin
declare @i bigint,@j bigint
select @i=max(序号) from ruku
while (@j<=@i)
update ruku set 序号=((select 序号 from deleted)-1)
end
FOR DELETE
AS
begin
declare @i bigint,@j bigint
select @i=max(序号) from ruku
while (@j<=@i)
update ruku set 序号=((select 序号 from deleted)-1)
end
1:没有给@i赋初值,这样它就是NULL,而NULL 与任何值的比较是 非
2:@i没有在循环中增加。
试一下
FOR DELETE
AS
begin
declare @i bigint
while (@i<=(select max(序号) from ruku)) --@I还没有值,
if @i >(select 序号 from deleted)
update ruku set 序号=((select 序号 from ruku)-1)
end
FOR DELETE
AS
begin
declare @i bigint
set @i=(select 序号 from deleted)
while (@i<=(select max(序号) from ruku))
set @i=@i+1
if @i >(select 序号 from deleted)
update ruku set 序号=((select 序号 from ruku)-1)
end
各位大哥,我改了还是不可以,提示是“子查询返回的值多于一个,这种情况不允许的”
FOR DELETE
AS
begin
declare @i int,@j int
select @j=序号 from deleted
declare ACursor cursor for select 序号 from ruku
open ACursor
fetch next from ACursor into @i
while @@Fetch_Status=0
begin if @i >@j
update ruku set 序号=@i-1 where 序号=@i
fetch next from ACursor into @i
end
close ACursor
deallocate ACursor
end
go
FOR DELETE
AS
begin
declare @i bigint
select @i=序号 from deleted
update ruku set 序号 =序号 -1 where 序号 >@i
end
FOR DELETE
AS
begin
declare @i bigint
set @i=(select 序号 from deleted)
while (@i<=(select max(序号) from ruku))
begin
set @i=@i+1
update ruku set 序号=序号-1
end
end