按行触发,将取最大值的过程改成使用序列就可以了-- Create sequence create sequence S_test minvalue 1 maxvalue 9999999999999999999999999 start with 1 increment by 1 cache 20;-- Create trigger create or replace trigger t_test before insert on table_test for each row begin Select s_test.nextval into :New.ID From dual; end t_test;
create or replace trigger tri_t before insert on t for each row declare id_str varchar2(10); id_num number; begin select max(id) into id_str from t; id_num:=to_number(id_str,'9999999999'); id_num:=id_num+1; id_str:=to_char(id_num); --update t set id=id_str where id is null; update t set id=id_str where id is null and t1=:new.t1; end test_insert;SQL> select * from t;T T ID - - ---------- A B a a 2 a a 3SQL> insert into t values('A','a',4);已创建 1 行。SQL> select * from t;T T ID - - ---------- A B 4 A a 4 a a 2 a a 3不知道楼主的问题在哪里?
create sequence S_test
minvalue 1
maxvalue 9999999999999999999999999
start with 1
increment by 1
cache 20;-- Create trigger
create or replace trigger t_test
before insert on table_test
for each row
begin
Select s_test.nextval into :New.ID From dual;
end t_test;
before insert on t for each row
declare
id_str varchar2(10);
id_num number;
begin
select max(id) into id_str from t;
id_num:=to_number(id_str,'9999999999');
id_num:=id_num+1;
id_str:=to_char(id_num);
--update t set id=id_str where id is null;
update t set id=id_str where id is null and t1=:new.t1;
end test_insert;SQL> select * from t;T T ID
- - ----------
A B
a a 2
a a 3SQL> insert into t values('A','a',4);已创建 1 行。SQL> select * from t;T T ID
- - ----------
A B 4
A a 4
a a 2
a a 3不知道楼主的问题在哪里?
改为插入后触发你就会看到问题了~
commit后再查最大的。只有用自治事务了。