然而执行insert into ic values(444,858,1234567890,1,201)时没有错误。
444,858,1234567890,1,201即上面查找的记录
444,858,1234567890,1,201即上面查找的记录
解决方案 »
- 建表添加 注释报错
- oracle在存储过程里创建视图
- oracle的xml解析包xmlparserv2.jar怎么这么难用?oracle哪个sb做的?
- 在update的时候,怎么让别的表条件也要满足的时候才能执行呢?
- 新手提问:找不到init.ora,请问ORACLE92的参数文件叫什么名字? 怎么修改?
- LINUX下,安装ORACLE问题
- 求救,有关查询及分页问题
- 在数据库中的基本资料表,是否要设为其它表的外键.
- 創建 Oracle Trigger 時出錯??When Instering ……
- oracle8怎样在p4的电脑中安装(操作系统为win2000)?
- 如何实现这样的拷贝功能?
- 求救?help??????在线等待!
1. 是否在TAXWARE中存在ReceiverNo. 那完全可以用一个外键来完成约束.
2. 是否在IC表中存在相同的ReceiverNo但不同IcCode的纪录,那就意味着ReceiverNo是唯一的,添加一个UNIQUE的约束不就可以了吗?
2、TAXWARE中ReceiverNo与State合起来也不唯一,允许有多条相同ReceiverNo且State=2的记录。
3、应该是ReceiverNo、IcCode、IcFlag三者合起来唯一,其中ReceiverNo、IcCode必须一一对应,允许有IcCode相同而IcFlag不同的记录
WHERE ReceiverNo = :NEW.ReceiverNo
AND (IcCode < :NEW.ICCODE OR IcCode > :NEW.ICCODE);是不正确的。象你这样的情况,你可以对该表中的ReceiverNo建立唯一索引
SQL> create table taxware
2 (receiverno varchar2(10),
3 state number(1));
Table created.SQL> create table ic
2 (id number(10),
3 receiverno varchar2(10),
4 iccode varchar2(10),
5 icflag number(3));
Table created.SQL> create sequence ic_id;
Sequence created.SQL> CREATE OR REPLACE TRIGGER IC_TRIGGER
2 BEFORE INSERT ON IC
3 FOR EACH ROW
4 DECLARE
5 v_int INTEGER;
6 ErrNum INTEGER;
7 ErrMsg VARCHAR2(2000);
8 ReceiverNoErr EXCEPTION;
9 IcCodeErr EXCEPTION;
10 BEGIN
11 SELECT COUNT(ReceiverNo) INTO v_int FROM TaxWare
12 WHERE ReceiverNo = :NEW.ReceiverNo AND State = 0;
13 IF v_int = 0 THEN
14 RAISE ReceiverNoErr;
15 END IF;
16 SELECT COUNT(ReceiverNo) INTO v_int FROM IC
17 WHERE ReceiverNo = :NEW.ReceiverNo
18 AND (IcCode < :NEW.ICCODE OR IcCode > :NEW.ICCODE);
19 IF v_int > 0 THEN
20 RAISE IcCodeErr;
21 END IF;
22 SELECT IC_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
23 EXCEPTION
24 WHEN ReceiverNoErr THEN
25 RAISE_APPLICATION_ERROR(-20990,'invalid ReceiverNo');
26 WHEN IcCodeErr THEN
27 RAISE_APPLICATION_ERROR(-20905,'invalid IcCode');
28 WHEN OTHERS THEN
29 ErrNum := SQLCODE;
30 ErrMsg := SUBSTR(SQLERRM,1,100);
31 RAISE_APPLICATION_ERROR(-20989,ErrNum ||''|| ErrMsg);
32 END;
33 /
Trigger created.SQL> insert into taxware values ('858',0);
1 row created.SQL> insert into ic values (444,'858','1234567890',1);
1 row created.SQL> commit;
Commit complete.SQL> create table icbck as select * from ic;
Table created.SQL> insert into ic select * from icbck;
insert into ic select * from icbck
*
ERROR at line 1:
ORA-20989: -4091ORA-04091: table DEV.IC is mutating, trigger/function may not
see it
ORA-06512: at line 28
ORA-04088: error during execution of trigger 'DEV.IC_TRIGGER'这个问题是ORACLE为了防止对正在改动的表的操作而特意加入的限制条件.实际上,对行级触发器(FOR EACH ROW),只有BEFORE INSERT才能在单行插入的时候在触发器内部对本身表进行读操作,其他情况下(如BEFORE UPDATE/AFTER INSERT等)即使在单行操作的时候也不能对本表进行任何操作.我记得论坛中有一个帖子专门讨论过这个问题.
当用insert into ic select * from icbck;进行插入操作的时候,系统无法确定是否是单行插入操作,就会认为表本身已经被改动了,无法在触发器内部对表进行读操作,所以报错.
这个问题恐怕不能直接用纪录集来做数据源进行插入,而只能用INSERT ... VALUES来操作.如下:
SQL> declare
2 cursor cur_icbck is
3 select * from ic;
4 begin
5 for rec_ic in cur_icbck loop
6 insert into ic
7 values (rec_ic.id,rec_ic.receiverno,
8 rec_ic.iccode,rec_ic.icflag);
9 end loop;
10* end;
SQL> /PL/SQL procedure successfully completed.该段PL/SQL代码成功执行
SELECT COUNT(ReceiverNo) INTO v_int FROM IC
WHERE ReceiverNo = :NEW.ReceiverNo
AND (IcCode < :NEW.ICCODE OR IcCode > :NEW.ICCODE);
同意 Qihua_wu(小吴) 的说法,你这样做违反了Oracle的完整性约束。
建议你看一下《ORACLE PL/SQL程序设计》中第九章触发器的变化表部分,P229。