~~~数据序号更新问题~~

[qianduo]

现有一表Customer
RefNo User Group CustomerName
1 LDY A Abdf
2 LDY A Cwerw
3 LDY A Dfsdf
4 LDY A Ewer
5 LDY B Eddfsd
6 LDY B Fwer
7 LDY B Iwqe
8 LDY C Rwerw
9 LDY C Wfser
1 Tom A Mqweq
2 Tom A Oqwe
3 Tom A Wqwe
4 Tom B Feee
5 Tom B Fww
6 Tom C Fwer


RefNo是按每个User当中的Group 和CustomerName来排序的。
如果新增加一条记录或更改删除了其中的Group或CustomerName那么RefNo则要更新RefNo.
请求解决方案

[wgsasd311]

create trigger ti_tb on tb
for update,insert,delete
as
declare @tb table(id int identity(1,1),refno int,[user] varchar(20),[group] varchar(20),
customername varchar(20))
insert @tb([user],[group],customerName)
select [user],[group],customerName from tb order by [user],[group],customerName
update a set refno=(select count(*) from @tb where [user]=a.[user] and [id]<=a.[id])
from @tb a
truncate table tb
insert tb(refno,[user],[group],customerName)
select refno,[user],[group],customerName from @tb
go

[wgsasd311]

--上面语句,我在本机上测试通过了

[vivianfdlpw]

--创建测试环境
create table Customer
(
RefNo int,
[User] varchar(10),
[Group] varchar(10),
CustomerName varchar(10)
)
go

--创建触发器
create trigger tr
on Customer
for insert,update,delete
as

update t
set RefNo=(select count(1)
from Customer
where [User]=t.[User]
and CustomerName<=t.CustomerName)
from Customer t
where t.[User] in(select [User] from inserted
union
select [User] from deleted
)


if @@error<>0
begin
rollback tran
raiserror('操作失败，错误号为%d',12,1,@@error)
end
go


--纪录插入测试
insert Customer select 1,'LDY','A','Abdf'
insert Customer select 1,'LDY','A','Cwerw'
insert Customer select 1,'LDY','A','Dfsdf'
insert Customer
select 1,'LDY','A','Ewer' union
select 1,'LDY','B','Eddfsd' union
select 1,'LDY','B','Fwer' union
select 1,'LDY','B','Iwqe' union
select 1,'LDY','B','Rwerw'

--查看
select * from Customer order by RefNo

/*
RefNo User Group CustomerName
----------- ---------- ---------- ------------
1 LDY A Abdf
2 LDY A Cwerw
3 LDY A Dfsdf
4 LDY B Eddfsd
5 LDY A Ewer
6 LDY B Fwer
7 LDY B Iwqe
8 LDY B Rwerw

(8 row(s) affected)
*/

--更新测试
update Customer
set CustomerName='Weberg' where RefNo=3

--查看
select * from Customer order by RefNo

/*
RefNo User Group CustomerName
----------- ---------- ---------- ------------
1 LDY A Abdf
2 LDY A Cwerw
3 LDY B Eddfsd
4 LDY A Ewer
5 LDY B Fwer
6 LDY B Iwqe
7 LDY B Rwerw
8 LDY A Weberg

(8 row(s) affected)
*/

--删除测试
delete from Customer where RefNo<=3

--查看
select * from Customer order by RefNo

/*
RefNo User Group CustomerName
----------- ---------- ---------- ------------
1 LDY A Ewer
2 LDY B Fwer
3 LDY B Iwqe
4 LDY B Rwerw
5 LDY A Weberg

(5 row(s) affected)
*/


--删除测试环境
drop table Customer

[zzxiaoma]

最好建个按钮实现重新排序

[天地客人]

哈哈,做个触发器,楼主的就行!达到楼主的功能了吗?

[qianduo]

触发器会不会不太稳定？这个表有很多记录，里面其实还有很多其它的字段，现在也只是想如果改变了Group 和CustomerName这两个字段，才会重新生成RefNO.
我开始想的是做一个存储过程，传入一个User，然后就在这个User的所有记录按Group 和CustomerName排序然后更新RefNO.这样在程序中也可以控制。

[qianduo]

to vivianfdlpw() :

update t
set RefNo=(select count(1)
from Customer
where [User]=t.[User]
and CustomerName<=t.CustomerName)
from Customer t
where t.[User] in(select [User] from inserted
union
select [User] from deleted
)

我的这个是先按Group 再按CustomerName来排序的.你的结果是只按CustomerName排序

[子陌红尘]

CREATE TRIGGER TRG_Customer
ON Customer
FOR INSERT,UPDATE,DELETE
AS
BEGIN
UPDATE A
SET
A.RefNo = (SELECT COUNT(*) FROM Customer WHERE Group<A.Group OR (Group=A.Group AND CustomerName<=A.CustomerName))
FROM
Customer A
WHERE
A.User IN(SELECT User FROM INSERTED)
OR
A.User IN(SELECT User FROM DELETED)

END