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

在以数据库为基础的应用程序开发中，分页是一个比较常用的操作,
可惜的是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
AS
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
AS
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
AS
  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列，只是为了
占用内存而已
下面是我的测试用例:

insert into orderemp(rownum,empid,emptxt,empdate)
values( 1, 173,@#ddfdd@#,getdate())
insert into orderemp(rownum,empid,emptxt,empdate)
values( 1, 123,@#ddfdd@#,getdate())
insert into orderemp(rownum,empid,emptxt,empdate)
values( 1, 163,@#ddfdd@#,getdate())
insert into orderemp(rownum,empid,emptxt,empdate)
values( 1, 153,@#ddfdd@#,getdate())
insert into orderemp(rownum,empid,emptxt,empdate)
values( 1, 143,@#ddfdd@#,getdate())
select    * from orderemp  order by rownum
delete from  orderemp where   empid>150 and empid<170
select    * from orderemp  order by rownum
至于更新的语句吗
只要不更新ROWNUM列，就不用处理了

注：一定要把数据库的：服务器设置--->服务器行为--->第二个选项不要选中

原文转自：http://www.ltesting.net