Oracle上没有自增字段,可以使用索引和触发器来达到此目的 第一步:创建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 new.country_id=s_country_id.nextval; end;
1,create sequence s_id increment by 1 start with 1 maxvalue 999999999; 2,insert into jc(jcid) values(s_id.nextval);
create table t_test(id int ,name varchar(100)); CREATE SEQUENCE sq_test INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE NOCACHE NOORDER ;
CREATE OR REPLACE TRIGGER trg_test BEFORE INSERT ON t_test FOR EACH ROW BEGIN select sq_test.NEXTVAL INTO :NEW.id FROM DUAL; END;
insert into t_test(name) select 'a' from dual union select 'b' from dual;
CREATE OR REPLACE TRIGGER trg_test BEFORE INSERT ON jc FOR EACH ROW BEGIN select sq_test.NEXTVAL INTO :NEW.jcid FROM DUAL; END; ON后面是你的表明,NEW后面是你要自增的字段
ON后面是你的表名,:NEW后面是你要自增的字段
CREATE OR REPLACE TRIGGER trg_test BEFORE INSERT ON t_test FOR EACH ROW BEGIN select sq_test.NEXTVAL INTO :NEW.id FROM DUAL; END;
第一步:创建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
new.country_id=s_country_id.nextval;
end;
2,insert into jc(jcid) values(s_id.nextval);
CREATE SEQUENCE sq_test INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
NOCACHE NOORDER ;
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON t_test
FOR EACH ROW
BEGIN
select sq_test.NEXTVAL INTO :NEW.id FROM DUAL;
END;
insert into t_test(name) select 'a' from dual union select 'b' from dual;
select * from t_test;
/*
ID NAME
1 a
2 b*/
结果错误提示——ORA-04089:无法对SYS拥有的对象创建触发器。我该如何解决这个问题?
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON jc
FOR EACH ROW
BEGIN
select sq_test.NEXTVAL INTO :NEW.jcid FROM DUAL;
END; ON后面是你的表明,NEW后面是你要自增的字段
BEFORE INSERT ON t_test
FOR EACH ROW
BEGIN
select sq_test.NEXTVAL INTO :NEW.id FROM DUAL;
END;
运行这个的时候出现这个错误
结果错误提示——ORA-04089:无法对SYS拥有的对象创建触发器。我该如何解决这个问题?