Automatically generate line numbers for tables in a database----lay the groundwork for pagination (Oracle-like rownum)

oracle| Pagination | data | database
Paging is a more common operation in database based application development,
Unfortunately, the corresponding RowNum attribute in Oracle is not available in SQL Server2000.
The younger brother uses "triggers" to generate a rownum column]
Can be used, of course, with the following SQL statement can also generate page I, every page n row, Tid is the primary key column,
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
It's okay, but I think it's a different way.
So there's the idea of automatically generating rownum columns.
eg
Build table:
CREATE TABLE [dbo]. [Orderemp] (
[RowNum] [INT] Not NULL,---and the column requires a uniqueness constraint
[Ordid] [INT] IDENTITY (1, 1) not NULL,---primary key column
[EmpID] [INT] Not NULL,
[Emptxt] [varchar] (m) COLLATE chinese_prc_ci_as not NULL,
[Empdate] [DateTime] Not NULL---Build a clustered index on this column
) on [PRIMARY]
----------the trigger to handle the INSERT statement
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

"Triggers"---deleted
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-----Can not be dropped, as for why, we have to try to know
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
---This trigger modifies the RowNum column in the table after the user opens the table directly from SQL Enterprise Manager
---may not be entirely
----But when you manipulate a table through an UPDATE statement, there is no problem as long as you do not modify the rownum column
CREATE TRIGGER ORDEREMPUPDTRG
On Orderemp
For UPDATE
As
Begin
IF UPDATE (rownum)
RAISERROR (' rownum column cannot be modified by itself! ', 16, 1)
ROLLBACK TRANSACTION

End
The stored procedures for adding new records are as follows:
Create PROCEDURE [Addorderemp]
(@empID [int],
@empTxt [varchar] (50),
@empDate [datetime])

As INSERT into [orderemp]
([RowNum], [EmpID], [Emptxt], [empdate])

VALUES
(1, @empID, @empTxt, @empDate)----"1" is necessary but will not affect the rownum column, just to
It takes up memory.
Here is my test case:

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
As for the updated statement?
As long as you don't update the rownum column, you don't have to deal with

Note: Be sure to put the database: Server Settings---> Server behavior---> The second option do not select