请教有经验者帮我解决下这个问题，以下是我写的触发器，只能insert不能update，update会出错，以下是出错信息，但是不知道出错在哪里，请高手帮我查看下，不甚感激，谢谢！在线等，急急急
create or replace trigger resource_typeTri
before insert or update on resource_type
for each row
declare
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;
SQL> update resource_type
2 set code='transport'
3 where resource_type_id=0;update resource_type
set code='transport'
where resource_type_id=0ORA-04091: 表 EGIS_ADMIN.RESOURCE_TYPE 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "EGIS_ADMIN.RESOURCE_TYPETRI", line 6
ORA-04088: 触发器 'EGIS_ADMIN.RESOURCE_TYPETRI' 执行过程中出错
create or replace trigger resource_typeTri
before insert or update on resource_type
for each row
declare
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;
SQL> update resource_type
2 set code='transport'
3 where resource_type_id=0;update resource_type
set code='transport'
where resource_type_id=0ORA-04091: 表 EGIS_ADMIN.RESOURCE_TYPE 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "EGIS_ADMIN.RESOURCE_TYPETRI", line 6
ORA-04088: 触发器 'EGIS_ADMIN.RESOURCE_TYPETRI' 执行过程中出错
before insert or update on resource_type
for each row
declare
v_code varchar2(50);
v_name varchar2(50);
pragma autonomous_transaction;
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;
2 before insert or update on resource_type
3 for each row
4 declare
5 v_code varchar2(50);
6 v_name varchar2(50);
7 pragma autonomous_transaction;
8 begin
9 v_code:=:new.code;
10 select max(substr(code,-3,3))+1001 into v_name from resource_type where code like v_code||'%';
11 if v_name is null then
12 :new.code:=v_code||'_'||'001';
13 else
14 :new.code := v_code||'_'||to_char(substr(v_name,-3,3));
15 end if;
16 end;
17 /Trigger createdSQL> insert into resource_type(resource_type_id,code) values(seq_resource_type.nextval,'transport');1 row insertedSQL>
SQL> update resource_type
2 set code='transport'
3 where resource_type_id=0
4 ;update resource_type
set code='transport'
where resource_type_id=0ORA-00001: 违反唯一约束条件 (EGIS_ADMIN.RESOURCE_TYPE_CODE_IDX)高手帮帮忙吧
drop index RESOURCE_TYPE_CODE_IDX;Index droppedSQL>
SQL> update resource_type
2 set code='transport'
3 where resource_type_id=0
4 ;1 row updatedSQL> insert into resource_type(resource_type_id,code) values(seq_resource_type.nextval,'transport');1 row insertedSQL> select * from resource_type;RESOURCE_TYPE_ID CODE NAME STATUS DESCRIPTION
---------------- -------------------------------------------------- ------------------------------ ------ --------------------------------------------------------------------------------
0 transport_006 巴士 1 description
1 transport_002 麵包車 1 description1
2 transport_003 卡車 1 description2
3 consumable_001 小吃 1 description3
4 consumable_002 飲料 1 description4
5 consumable_003 餐飲 1 description5
6 consumable_004 血袋類型 1 description6
7 equipment_001 椅子 1 description7
8 equipment_002 床 1 description8
9 equipment_003 桌子 1 description9
10 equipment_004 電腦 1 description110
60 consumable_005 xc
61 transport_004 df
103 transport_005
104 transport_006
80 equipment_007 equipment3
62 consumable_006 dfg
63 consumable_007 consumable1
100 equipment_008 cxxx
102 equipment_009 货车 货车
58 equipment_005 equipment
59 equipment_006 equipment2 22 rows selectedSQL>
SQL> update resource_type
2 set code='transport'
3 where resource_type_id=3;1 row updatedSQL> select * from resource_type;RESOURCE_TYPE_ID CODE NAME STATUS DESCRIPTION
---------------- -------------------------------------------------- ------------------------------ ------ --------------------------------------------------------------------------------
0 transport_006 巴士 1 description
1 transport_002 麵包車 1 description1
2 transport_003 卡車 1 description2
3 transport_006 小吃 1 description3
4 consumable_002 飲料 1 description4
5 consumable_003 餐飲 1 description5
6 consumable_004 血袋類型 1 description6
7 equipment_001 椅子 1 description7
8 equipment_002 床 1 description8
9 equipment_003 桌子 1 description9
10 equipment_004 電腦 1 description110
60 consumable_005 xc
61 transport_004 df
103 transport_005
104 transport_006
80 equipment_007 equipment3
62 consumable_006 dfg
63 consumable_007 consumable1
100 equipment_008 cxxx
102 equipment_009 货车 货车
58 equipment_005 equipment
59 equipment_006 equipment2 22 rows selectedSQL> commit;Commit completeSQL> create unique index RESOURCE_TYPE_CODE_IDX on resource_type(code);Index createdSQL> commit;Commit completeSQL> insert into resource_type(resource_type_id,code) values(seq_resource_type.nextval,'transport');1 row insertedSQL>
SQL> update resource_type
2 set code='transport'
3 where resource_type_id=0;update resource_type
set code='transport'
where resource_type_id=0ORA-00001: 违反唯一约束条件 (EGIS_ADMIN.RESOURCE_TYPE_CODE_IDX)SQL> select * from resource_type;RESOURCE_TYPE_ID CODE NAME STATUS DESCRIPTION
---------------- -------------------------------------------------- ------------------------------ ------ --------------------------------------------------------------------------------
0 transport_001_001 巴士 1 description
1 transport_002 麵包車 1 description1
2 transport_003 卡車 1 description2
3 consumable_001_001 小吃 1 description3
4 consumable_002 飲料 1 description4
5 consumable_003 餐飲 1 description5
6 consumable_004 血袋類型 1 description6
7 equipment_001 椅子 1 description7
8 equipment_002 床 1 description8
9 equipment_003 桌子 1 description9
10 equipment_004 電腦 1 description110
60 consumable_005 xc
61 transport_004 df
103 transport_005
104 transport_006
105 transport_007
80 equipment_007 equipment3
62 consumable_006 dfg
63 consumable_007 consumable1
100 equipment_008 cxxx
102 equipment_009 货车 货车
58 equipment_005 equipment
59 equipment_006 equipment2 23 rows selectedSQL> select * from resource_type;
create or replace trigger resource_typeTri
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;