ID Name OrderID
39 会议展览 1
72 会展新闻 2
265 排序测试 3
266 排序测试2 4
38 商品进出口价格 5
7 市场动态 6
10 招标采购 7
25 产品供求信息 8
56 调研报告 9
8 招商引资 10
6 通知公告 11我想做一个update触发器,当修改orderid的位置后,其它位置自动更新,例如:把orderid等于3的这个值改成8后,那么orderid从4到8的值都减去1,该如何做?
39 会议展览 1
72 会展新闻 2
265 排序测试 3
266 排序测试2 4
38 商品进出口价格 5
7 市场动态 6
10 招标采购 7
25 产品供求信息 8
56 调研报告 9
8 招商引资 10
6 通知公告 11我想做一个update触发器,当修改orderid的位置后,其它位置自动更新,例如:把orderid等于3的这个值改成8后,那么orderid从4到8的值都减去1,该如何做?
on ta
instead of update
as
begin
declare @i int,@j int
select @i = d.OrderID ,@j = i.orderid from inserted i,deleted d where i.id = d.id
update ta
set orderid = orderid - 1
where orderid between @i and @j
update a
set orderid = i.orderid
from ta a,inserted i where a.id =b.id
end
go
for updateif update(oderid) update t set oderid=oderid-1 from t ,deleted,inserted
where t.id=deleted.id and t.id=inserted.id
and t.orerid>deleted.orderid and t.orerid<inserted.orderid
for update if update(oderid)
begin--orderid 由小变大
update t set oderid=oderid-1 from t ,deleted,inserted
where t.id=deleted.id and t.id=inserted.id
and t.orerid>deleted.orderid and t.orerid <inserted.orderid
and deleted.orderid<inserted.orderid--orderid 由大变小update t set oderid=oderid+1 from t ,deleted,inserted
where t.id=deleted.id and t.id=inserted.id
and t.orerid<deleted.orderid and t.orerid >inserted.orderid
and deleted.orderid>inserted.orderidend
declare @orderid2 as int
set @orderid1 = 3
set @orderid1 = 8update tb set OrderID = OrderID - 1 where OrderID > @OrderID1 and OrderID < @OrderID2
update tb set OrderID = @OrderID2 where OrderID = @OrderID1
declare @orderid2 as int
set @orderid1 = 3
set @orderid2 = 8 update tb set OrderID = OrderID - 1 where OrderID > @OrderID1 and OrderID < @OrderID2
update tb set OrderID = @OrderID2 where OrderID = @OrderID1 如果要考虑@orderid1 ,@orderid2的大小的话,自己先用if判断一下.
------------------------
为啥只改4到8的,9到11的怎么不改呢?
create trigger tri_upd on t
for update if update(oderid)
begin --orderid 由小变大
update t set oderid=oderid-1 from t ,deleted,inserted
where t.id=deleted.id and t.id=inserted.id
and t.orerid>=deleted.orderid and t.orerid <inserted.orderid --加个=号
and deleted.orderid <inserted.orderid --orderid 由大变小 update t set oderid=oderid+1 from t ,deleted,inserted
where t.id=deleted.id and t.id=inserted.id
and t.orerid <deleted.orderid and t.orerid >=inserted.orderid --加个=号
and deleted.orderid>inserted.orderid end
提供的从小到大修改是没有问题,要是从大改到小就有问题了
其它人提供的都有些小问题!