求SQL2000表求触发器顺序排序
求SQL2000表求触发器顺序排序
如表 e_orderdp
billid ,itemno,goodsid,unitqty,
4562 , 1
4578 , 2
4584 , 6
4595 ,2
45106 ,300
45117 ,350
45128 ,200
因为插单明细\补单 令到itemno 顺序不是按顺序1,2,3,4,5,......
求触发器
billid ,itemno,goodsid,unitqty,
4512 , 1
4528 , 2
4534 , 6
4545 ,2
4556 ,300
4567 ,350
4578 ,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
/*
billiditemnogoodsidunitqty
45121
45282
45346
45452
4556300
4567350
4578200
*/
