Oracle利用触发器和sequence实现主键自增长

Oracle利用触发器和sequence实现主键自增长

建立表

-- Create table
create table GEO_BOOKMARK
(
F_ID NUMBER not null,
F_NAME VARCHAR2(100) not null,
F_REMARK VARCHAR2(200),
F_XMIN NUMBER not null,
F_YMIN NUMBER not null,
F_XMAX NUMBER not null,
F_YMAX NUMBER not null,
F_LEVEL NUMBER not null,
F_LAYERIDS VARCHAR2(50)
)
tablespace PRJZC927
pctfree 10
initrans 1
maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table GEO_BOOKMARK
add constraint F_PKID primary key (F_ID)
using index
tablespace PRJZC927
pctfree 10
initrans 2
maxtrans 255;

建立Sequence

-- Create sequence
create sequence SEQ_GEO_BOOKMARK
minvalue 1
maxvalue 9999999999999
start with 1
increment by 1
cache 20
order;

建立触发器

create or replace trigger trg_geo_bookmark_id
before insert on geo_bookmark
for each row
begin
SELECT seq_geo_bookmark.nextval INTO :new.f_id FROM dual;
end;