In the database-based application development, paging is a relatively common operation, but unfortunately, the corresponding ROWNUM attributes in the SQL Server 2000 are available. The younger brother uses a "trigger" to generate a row race] Bistens to one, Of course, you can also generate the following SQL statement, you can generate 第 第, TID, TID is the primary key column, select top n * from tab where strwhere and tid> (SELECT TOP (I-1) * n Tid from Tab WHERE STRWHERE ORDER BY TID) AS T)) ORDER BY TID can also be, but I don't want to use another method, I have no idea to automatically generate ROWNUM columns EG: Construction Table: CREATE TABLE [DBO] [Orderemp] ([ROWNUM] [INT] NOT NULL, --- At the same time, the column requires uniqueness constraints [ORDID] [INT] Identity (1, 1) Not null, --- Main key column [Empid] [INT ] NOT NULL, [EMPTXT] [VARCHAR] (50) Collate Chinese_PRC_CI_AS NOT NULL, [EMPDATE] [DATETIME] NOT NULL --- Upset "Gathering Index") on [PRIMARY] -------------------------------------------------------------------------------------------------------------------- - processing of the trigger insert statements CREATE tRIGGER orderEmpAddTrgON orderEmpinstead of INSERTASbegin 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.empda TE from Inserted I end
--- deleted 'trigger' CREATE TRIGGER orderEmpDelTrgON dbo.orderEmpFOR DELETEASbegin set nocount on declare @rw int declare @tab table (rw int) insert into @tab select rownum from deleted order by rownum desc ----- not Drop, as for why, everyone will try to know the declare cp cursor for select rw from @tab open cp fetch next from cp @rw while @@ fetch_status = 0 begin update Ordeering set rownum = rownum-1 where rownum> @RW Fetch next from cp info @rw end, cp deallocate cp set nocount offend --- This trigger is to modify the ROWNUM column in the table after opening the table from the SQL Enterprise Manager to the SQL Enterprise Manager --- may not be fully --- During operation of the UPDATE statement, as long as the ROWNUM column does not change the Create Trigger ORDEREMPUPDTRGON ORDEREMPFOR UPDATE AS BEGIN if Update (ROWNUM) RAISERROR ('rownum column can not be modified!', 16, 1 The stored procedure adds new recorded stored procedures as follows: Create Procedure [AddordEREMP] (@empid [int], @emptxt [varcha] (50), @empdate [datetime])
