在以數據庫為基礎的應用程序開發中
可惜的是SQL Server
用
勉強可以一用
select top n * from tab
where strWhere and tid>(select max(tid)
from (select top (i
)
order by tid
也可以
因此就有自動生成ROWNUM列的想法
eg:
建表
CREATE TABLE [dbo]
[rownum] [int] NOT NULL
[ordID] [int] IDENTITY (
[empID] [int] NOT NULL
[empTxt] [varchar] (
[empDate] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TRIGGER orderEmpAddTrg
ON orderEmp
instead of INSERT
AS
begin
declare @rw int
select @rw=
select @rw=max(rownum) from orderEmp
if(@rw is null)
select @rw=
select @rw=@rw+
INSERT INTO orderEmp(rownum
SELECT @rw
FROM inserted i
end
CREATE TRIGGER orderEmpDelTrg
ON dbo
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=
begin
update orderEmp
set rownum=rownum
where rownum>@rw
fetch next from cp into @rw
end
close cp
deallocate cp
set nocount off
end
CREATE TRIGGER orderEmpUpdTrg
ON orderEmp
FOR UPDATE
AS
begin
IF UPDATE (rownum)
RAISERROR (
ROLLBACK TRANSACTION
end
添加新記錄的存儲過程如下
create PROCEDURE [addOrderEmp]
( @empID [int]
@empTxt [varchar](
@empDate [datetime])
AS INSERT INTO [orderEmp]
( [rownum]
VALUES
(
占用內存而已
下面是我的測試用例:
insert into orderemp(rownum
values(
insert into orderemp(rownum
values(
insert into orderemp(rownum
values(
insert into orderemp(rownum
values(
insert into orderemp(rownum
values(
select * from orderemp order by rownum
delete from orderemp where empid>
select * from orderemp order by rownum
至於更新的語句嗎
只要不更新ROWNUM列
注
From:http://tw.wingwit.com/Article/program/Oracle/201311/16686.html