create or replace trigger resource_type_tri
before insert or update on resource_type
for each row
declare
pragma autonomous_transaction;
v_code varchar2(50);
v_name varchar2(50);
begin
v_code:=:new.code;
select max(substr(code,-3,3))+1001 into v_name from resource_type where code like v_code||'%';
if v_name is null then
:new.code:=v_code||'_'||'001';
else
:new.code := v_code||'_'||to_char(substr(v_name,-3,3));
end if;
end;
这是我编的触发器,我本想数据成以下那样自增的
transport_001
transport_002
transport_003
consumable_001
consumable_002
consumable_003
consumable_004
equipment_001
equipment_002
equipment_003
equipment_004
consumable_005
但是我写的触发器,数据却成了一下这样
transport_013_014
transport_002_003
transport_015_016
consumable_009_010_001
consumable_002_003_001
consumable_010_011_001
consumable_004_006_007_001
equipment_001_002
equipment_002_003_001
equipment_003_004
equipment_004_005_001
consumable_005_004
我不知道触发器到底哪里出错了,希望大侠帮帮忙,感激不尽,谢谢!在线等急急
before insert or update on resource_type
for each row
declare
pragma autonomous_transaction;
v_code varchar2(50);
v_name varchar2(50);
begin
v_code:=:new.code;
select max(substr(code,-3,3))+1001 into v_name from resource_type where code like v_code||'%';
if v_name is null then
:new.code:=v_code||'_'||'001';
else
:new.code := v_code||'_'||to_char(substr(v_name,-3,3));
end if;
end;
这是我编的触发器,我本想数据成以下那样自增的
transport_001
transport_002
transport_003
consumable_001
consumable_002
consumable_003
consumable_004
equipment_001
equipment_002
equipment_003
equipment_004
consumable_005
但是我写的触发器,数据却成了一下这样
transport_013_014
transport_002_003
transport_015_016
consumable_009_010_001
consumable_002_003_001
consumable_010_011_001
consumable_004_006_007_001
equipment_001_002
equipment_002_003_001
equipment_003_004
equipment_004_005_001
consumable_005_004
我不知道触发器到底哪里出错了,希望大侠帮帮忙,感激不尽,谢谢!在线等急急
transport_002
transport_003
consumable_001
consumable_002
consumable_003
consumable_004
equipment_001
equipment_002
equipment_003
equipment_004
consumable_005
就是要这样的一直自增,插入和更新数据都要自增,谢谢你,麻烦你帮我看看怎么写
before insert or update on resource_type
for each row
as
pragma autonomous_transaction;
v_code varchar2(50);
v_name varchar2(50);
begin
v_code:=:new.code; select max(substr(code,-3,3)) into v_name from resource_type where code like
v_code||'%'; if v_name is null then
:new.code:=v_code||'_'||'001';
else
:new.code := v_code||'_'||lpad(to_char((to_number(v_name)+1)),3,'0');
end if; end;
update resource_type
set code='transport'
where resource_type_id=0ORA-00001: 违反唯一约束条件 (EGIS_ADMIN.RESOURCE_TYPE_CODE_IDX)
有可能是我没有理解你的意思!
比如说:你code列插入或修改的数据的格式。
substr(code,-3,3)
这里应该是substr(trim(code),-3,3)
另外最后要加个commit吧,这里用了自治事务
set code='transport'
where resource_type_id=0
你resource_type_id=0的记录是不是不止一条哟?并且
code是主键!
SQL> insert into resource_type(resource_type_id,code,name) values(seq_resource_type.nextval,'transport','的士');1 row insertedSQL> insert into resource_type(resource_type_id,code,name) values(seq_resource_type.nextval,'transport','小车');insert into resource_type(resource_type_id,code,name) values(seq_resource_type.nextval,'transport','小车')ORA-00001: 违反唯一约束条件 (EGIS_ADMIN.RESOURCE_TYPE_CODE_IDX)为什么一直报这个错,这个触发器到底有什么问题,希望大侠帮忙解决下,已经很久了却不能得以解决,很急用,帮帮忙吧,谢谢了
insert into t values(4)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TSTUSER.IDX_T)
SQL> spool off;很显然,对于unique index,通过简单的设置参数是不能解决问题的,要解决unique index 失效的问题,只能通过重建索引来实现试试
create or replace trigger resource_type_tri
before insert or update on resource_type
for each row
declare
pragma autonomous_transaction;
v_code varchar2(50);
v_name varchar2(50);
begin
v_code:=:new.code;
select max(substr(code,-3,3))+1001 into v_name from resource_type where code like v_code||'%';
if v_name is null then
:new.code:=v_code||'_'||'001';
else
:new.code := v_code||'_'||to_char(substr(v_name,-3,3));
end if;
commit;
end;
为什么一直不行,到低是哪里错了,这个已经困扰我好久了,开发代码都写不下去了,麻烦大侠帮我看看,感谢了,在线等急急
before insert or update on resource_type
for each row
declare
v_code varchar2(50);
v_name number;
v_cnt number;
begin
v_code:=:new.code;
select instr(code,'_') into v_name
from resource_type
where code like v_code||'%'
and rownum=1;
if v_name=0 then
:new.code:=v_code||'_'||'001';
else
select max(substr(code,-3,3)) into v_cnt
from resource_type
where code like v_code||'%';
v_cnt:=v_cnt+1;
:new.code:= v_code||'_'||lpad(v_cnt,3,0);
end if;
exception when no_data_found then
:new.code:=v_code||'_'||'001';
end;
/
2、自治事务是一个独立的进程,在insert或者update提交以前,该纪录对自治进程是不可见的,所以你当你insert或update多条记录而不是每次commit的话,select max(substr(code,-3,3))查不到还未提交的记录,所以获得的id是重复的,就造成了unique constraint violation。
这个问题。
create or replace trigger resource_type_tri
before insert or update on resource_type
for each row
declare
pragma autonomous_transaction;
v_code varchar2(50);
v_name varchar2(50);
begin
v_code:=:new.code;
select max(substr(code,-3,3))+1001 into v_name from resource_type where code like v_code||'%';
if v_name is null then
:new.code:=v_code||'_'||'001';
else
:new.code := to_char(substr(v_code,0,length(v_code)-4))||'_'||to_char(substr(v_name,-3,3));
end if;
commit;
end;