求SQL2000表求触发器顺序排序解决方案
求SQL2000表求触发器顺序排序
求SQL2000表求触发器顺序排序
如表 e_orderdp
billid ,itemno,goodsid,unitqty,
45 6 2 , 1
45 7 8 , 2
45 8 4 , 6
45 9 5 ,2
45 10 6 ,300
45 11 7 ,350
45 12 8 ,200
因为插单明细\补单 令到itemno 顺序不是按顺序1,2,3,4,5,......
求触发器
billid ,itemno,goodsid,unitqty,
45 1 2 , 1
45 2 8 , 2
45 3 4 , 6
45 4 5 ,2
45 5 6 ,300
45 6 7 ,350
45 7 8 ,200
以下脚本: 运用到触发器有些问题,不知道如何设置,是插入\或修改时更新呢?
UPDATE tb SET xitemno=(SELECT COUNT(1) FROM tb t WHERE t.mxitemno =m.mxitemno AND t.itemno<=m.itemno) FROM tb m select * from tb
------解决方案--------------------
是这样吗:
求SQL2000表求触发器顺序排序
如表 e_orderdp
billid ,itemno,goodsid,unitqty,
45 6 2 , 1
45 7 8 , 2
45 8 4 , 6
45 9 5 ,2
45 10 6 ,300
45 11 7 ,350
45 12 8 ,200
因为插单明细\补单 令到itemno 顺序不是按顺序1,2,3,4,5,......
求触发器
billid ,itemno,goodsid,unitqty,
45 1 2 , 1
45 2 8 , 2
45 3 4 , 6
45 4 5 ,2
45 5 6 ,300
45 6 7 ,350
45 7 8 ,200
以下脚本: 运用到触发器有些问题,不知道如何设置,是插入\或修改时更新呢?
UPDATE tb SET xitemno=(SELECT COUNT(1) FROM tb t WHERE t.mxitemno =m.mxitemno AND t.itemno<=m.itemno) FROM tb m select * from tb
------解决方案--------------------
是这样吗:
--drop table tb
create table tb
(billid int,itemno int,goodsid int,unitqty int)
go
--drop trigger trigger_tb
create trigger dbo.trigger_tb
on tb
for insert
as
update tb
set itemno = (SELECT COUNT(1) FROM tb t
WHERE t.billid =t1.billid
AND t.itemno <= t1.itemno)
from tb t1
inner join inserted i
on t1.billid = i.billid
go
insert into tb
select 45, 6, 2 , 1
union all select 45 ,7 ,8 , 2
union all select 45 ,8 ,4 , 6
union all select 45 ,9 ,5 ,2
union all select 45 ,10 ,6 ,300
union all select 45 ,11 ,7 ,350
union all select 45 ,12 ,8 ,200
--查询
select *
from tb
/*
billid itemno goodsid unitqty
45 1 2 1
45 2 8 2
45 3 4 6
45 4 5 2
45 5 6 300
45 6 7 350
45 7 8 200
*/