我有两张表
create table STD_STATS_TOTAL
(
SWITCH_ID NUMBER(4),
CALLING_DATE CHAR(14),
CALLING_TIME CHAR(2),
EXCHANGE_CODE VARCHAR2(6),
COUNTS NUMBER(14)
);create table WW_TEST
(
SWITCH_ID NUMBER(4),
CALLING_DATE CHAR(14),
CALLING_TIME CHAR(2),
EXCHANGE_CODE VARCHAR2(6),
COUNTS NUMBER(14)
);
insert into WW_TEST (SWITCH_ID, CALLING_DATE, CALLING_TIME, EXCHANGE_CODE, COUNTS)
values (1001, '20050720', '18', 'total', 10);
我对STD_STATS_TOTAL表建一个触发器
-------------以下是触发器-------------
create or replace trigger befor_ins_std_stats_total
before insert on std_stats_total
for each row
declare v_counts number(14);
begin
select sum(counts)
into v_counts
from std_stats_total
where switch_id = :new.switch_id and calling_date = :new.calling_date
and exchange_code = :new.exchange_code and calling_time = :new.calling_time ;
if v_counts != 0
then
delete from std_stats_total
where switch_id = :new.switch_id and calling_date = :new.calling_date
and exchange_code = :new.exchange_code and calling_time = :new.calling_time ;
:new.counts := v_counts + :new.counts;
end if ;
end befor_ins_std_stats_total;-----------------以上是触发器-------------------
这个触发器的功能是当插入一条记录时,检查表中是否有除了counts外,其他字段一样的记录,如果有的话,就将counts值相加,原来的记录删去。我执行:
insert into std_stats_total values (1001,'20050720','18','total',10);
语句的时候,触发器正常执行。但我执行这样的语句:insert into std_stats_total select * from ww_test;的时候却报错:
SQL> insert into std_stats_total select * from ww_testORA-04091: table BILL.STD_STATS_TOTAL is mutating, trigger/function may not see it
ORA-06512: at "BILL.BEFOR_INS_STD_STATS_TOTAL", line 6
ORA-04088: error during execution of trigger 'BILL.BEFOR_INS_STD_STATS_TOTAL'Commit completeSQL>
为何插入有具体值的记录时不报错,从表里查数据插入的时候要报错?要怎样修改?
create table STD_STATS_TOTAL
(
SWITCH_ID NUMBER(4),
CALLING_DATE CHAR(14),
CALLING_TIME CHAR(2),
EXCHANGE_CODE VARCHAR2(6),
COUNTS NUMBER(14)
);create table WW_TEST
(
SWITCH_ID NUMBER(4),
CALLING_DATE CHAR(14),
CALLING_TIME CHAR(2),
EXCHANGE_CODE VARCHAR2(6),
COUNTS NUMBER(14)
);
insert into WW_TEST (SWITCH_ID, CALLING_DATE, CALLING_TIME, EXCHANGE_CODE, COUNTS)
values (1001, '20050720', '18', 'total', 10);
我对STD_STATS_TOTAL表建一个触发器
-------------以下是触发器-------------
create or replace trigger befor_ins_std_stats_total
before insert on std_stats_total
for each row
declare v_counts number(14);
begin
select sum(counts)
into v_counts
from std_stats_total
where switch_id = :new.switch_id and calling_date = :new.calling_date
and exchange_code = :new.exchange_code and calling_time = :new.calling_time ;
if v_counts != 0
then
delete from std_stats_total
where switch_id = :new.switch_id and calling_date = :new.calling_date
and exchange_code = :new.exchange_code and calling_time = :new.calling_time ;
:new.counts := v_counts + :new.counts;
end if ;
end befor_ins_std_stats_total;-----------------以上是触发器-------------------
这个触发器的功能是当插入一条记录时,检查表中是否有除了counts外,其他字段一样的记录,如果有的话,就将counts值相加,原来的记录删去。我执行:
insert into std_stats_total values (1001,'20050720','18','total',10);
语句的时候,触发器正常执行。但我执行这样的语句:insert into std_stats_total select * from ww_test;的时候却报错:
SQL> insert into std_stats_total select * from ww_testORA-04091: table BILL.STD_STATS_TOTAL is mutating, trigger/function may not see it
ORA-06512: at "BILL.BEFOR_INS_STD_STATS_TOTAL", line 6
ORA-04088: error during execution of trigger 'BILL.BEFOR_INS_STD_STATS_TOTAL'Commit completeSQL>
为何插入有具体值的记录时不报错,从表里查数据插入的时候要报错?要怎样修改?
解决方案 »
- ORACLE数据库处于归档模式恢复时错误【ORA-01122: 数据库文件 4 验证失败】
- 怎么实现性别选择框
- oracle job 怎么不自动执行
- 本人创建一个表里面属性的约束问题
- 在Pro*c 编译时出现下面错误信息,请问实是什么错误,该怎么解决啊???
- 问一句比较麻烦的SQL语句
- oracle下启动服务报ORA-01122错的问题
- 在ORACEL中sql Analyze如何去另一台的数据库?
- 在c++的程序中调用SQL语句,出错?
- 請教﹕分析函數中能否實現我的需求......
- 利用透明网关使oracle9与sql server2000直连中遇到的问题,高手帮忙!
- ORA-01861给高分,急求解决方案
以插入数据之前为触发条件的触发器不允许对载体表进行sum操作