第一步:创建SEQUENCE
create sequence s_country_id increment by 1 start with 1 maxvalue 999999999;
第二步:创建一个基于该表的before insert 触发器,在触发器中使用该SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
create sequence s_country_id increment by 1 start with 1 maxvalue 999999999;
第二步:创建一个基于该表的before insert 触发器,在触发器中使用该SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
ORACLE没有象SQL SERVER中一样的自增加字段,
要实现只能通过SEQUENCE来实现1.创建序列:
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;2.使用触发器实现自增:
create or replace trigger your_seq_tri
before insert on your_table1 for each row
declare
next_id number;
begin
select your_seq.nextval into next_id from dual;
:new.id := next_id;
end;
create sequence test
increment by 1
start with 1
;2.使用触发器实现自增:
create or replace trigger tri_test
before insert on table
for each row
begin
select test.nextval into :new.列名 from dual;
end;
increment by 1
minvalue 1
maxvalue 99999999999
start with 1
/
create or replace trigger tri_test
before insert on table
for each row
begin
select test.nextval into :new.列名 from dual;
end;
/
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
NOCACHE NOORDER;CREATE OR REPLACE TRIGGER USERADD
BEFORE INSERT ON MEMBERRG
FOR EACH ROW
BEGIN
SELECT LPAD(USERID.NEXTVAL,4,'0') INTO :NEW.NUM FROM DUAL;
END;
可以通过采取以下的功能实现自动增加ID的功能
1.首先创建 sequence
create sequence seqmax increment by 1
2.使用方法
select seqmax.nextval ID from dual
就得到了一个ID
如果把这个语句放在 触发器中,就可以实现 和 ms sql 的自动增加ID相同的功能!