为数据库的表自动生成行号----为分页打好基础（仿Oracle的ROWNUM)

王朝oracle·作者佚名 2008-05-31

宽屏版字体: 小|中|大|超大

在以数据库为基础的应用程序开发中，分页是一个比较常用的操作,

可惜的是SQL Server2000中没有Oracle中相应的ROWNUM属性可用，

小弟用”触发器“生成一个ROWNUM列]

勉强可以一用，当然用如下的SQL语句也可以生成第i页,每页n行，tid是主键列，

select top n * from tab

where strWhere and tid>(select max(tid)

from (select top (i-1)*n tid from tab where strWhere order by tid ) as T)

)

order by tid

也可以，但是我想用另一种方法也未尝不可

因此就有自动生成ROWNUM列的想法

eg:

建表：

CREATE TABLE [dbo].[orderEmp] (

[rownum] [int] NOT NULL ,---同时该列要求有唯一性约束

[ordID] [int] IDENTITY (1, 1) NOT NULL ,---主键列

[empID] [int] NOT NULL ,

[empTxt] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[empDate] [datetime] NOT NULL ---此列上建“聚集索引“

) ON [PRIMARY]

----------对插入语句处理的触发器

CREATE TRIGGER orderEmpAddTrg

ON orderEmp

instead of INSERT

begin

declare @rw int

select @rw=0

select @rw=max(rownum) from orderEmp

if(@rw is null)

select @rw=0

select @rw=@rw+1

INSERT INTO orderEmp(rownum,empID,empTxt,empDate)

SELECT @rw, i.empID,i.empTxt,i.empDate

FROM inserted i

end

---删除的“触发器'

CREATE TRIGGER orderEmpDelTrg

ON dbo.orderEmp

FOR DELETE

begin

set nocount on

declare @rw int

declare @tab table(rw int)

insert into @tab

select rownum from deleted

order by rownum desc -----不可以掉，至于为什么，大家自己试试就知道了

declare cp cursor

for

select rw from @tab

open cp

fetch next from cp into @rw

while @@fetch_status=0

begin

update orderEmp

set rownum=rownum-1

where rownum>@rw

fetch next from cp into @rw

end

close cp

deallocate cp

set nocount off

end

---这个触发器是为屏掉用户直接从SQL企业治理器打开表后对表中的ROWNUM列进行修改

---可能不完全

----但是通过UPdate语句操作表的时，只要不修改rownum列是不会出现问题的

CREATE TRIGGER orderEmpUpdTrg

ON orderEmp

FOR UPDATE

begin

IF UPDATE (rownum)

RAISERROR ('ROWNUM列不可以自行修改!

', 16, 1)

ROLLBACK TRANSACTION

end

添加新记录的存储过程如下：

create PROCEDURE [addOrderEmp]

( @empID [int],

@empTxt [varchar](50),

@empDate [datetime])

AS INSERT INTO [orderEmp]

( [rownum], [empID], [empTxt], [empDate])

VALUES

( 1, @empID, @empTxt, @empDate)----“1“是一定要的但是不会影响ROWNUM列，只是为了

占用内存而已

下面是我的测试用例: