求一触发器实现TABLE中某一字段类似orcal的rownum功能?

[breezeforever]

如题

[ojuju10]

如果是ms 2005
row_number() over (order by col desc)

[gahade]

--在触发器中搞对性能有很大影响.一般前台软件都有这个功能吧

create trigger tr_表 on 表
for insert,delete
as
update 表
set 字段=(select count(*) from 表 b where a.id>=b.id)
from 表 a

[ojuju10]

如果是 ms 2000


select *,(select count(1) from tblname where a.id>=id) as num from tblname a

[breezeforever]

select *,(select count(1) from article_tbl a where a.art_id >= a.art_id) as num from article_tbl a
art_id title desc times CreateDate num
2 test1 test1 NULL 2007-07-19 00:00:00.000 2
10 test2 test2 NULL 2007-07-20 00:00:00.000 2
11 test3 test3 NULL 2007-07-23 00:00:00.000 2

我想要的结果是
art_id title desc times CreateDate num
2 test1 test1 NULL 2007-07-19 00:00:00.000 1
10 test2 test2 NULL 2007-07-20 00:00:00.000 2
11 test3 test3 NULL 2007-07-23 00:00:00.000 3

当删除其中一条记录时,num也自动更新:
art_id title desc times CreateDate num
2 test1 test1 NULL 2007-07-19 00:00:00.000 1

11 test3 test3 NULL 2007-07-23 00:00:00.000 2

[breezeforever]

select *,(select count(1) from article_tbl a where a.art_id >= a.art_id) as num from article_tbl a
art_id title desc times CreateDate num
2 test1 test1 NULL 2007-07-19 00:00:00.000 3
10 test2 test2 NULL 2007-07-20 00:00:00.000 3
11 test3 test3 NULL 2007-07-23 00:00:00.000 3

我想要的结果是
art_id title desc times CreateDate num
2 test1 test1 NULL 2007-07-19 00:00:00.000 1
10 test2 test2 NULL 2007-07-20 00:00:00.000 2
11 test3 test3 NULL 2007-07-23 00:00:00.000 3

当删除其中一条记录时,num也自动更新:
art_id title desc times CreateDate num
2 test1 test1 NULL 2007-07-19 00:00:00.000 1

11 test3 test3 NULL 2007-07-23 00:00:00.000 2