CREATE OR REPLACE TRIGGER DCMIS.TRI_SAP_FEEDER_ID
AFTER INSERT
ON DCMIS.AA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_IP VARCHAR2(15);
V_AA INT ;
BEGIN
--SELECT AA_NO INTO V_IP FROM DUAL;
SELECT COUNT(*) INTO v_aa from AA;
IF MOD(V_AA,2) =0 THEN
INSERT INTO FEEDER_SAP(FEEDER_NO,FLAT)
VALUES(:old.A,'N');
END IF;END;出错提示如下
ORA-04091: table DCMIS.AA is mutating, trigger/function may not see it
ORA-06512: at "DCMIS.TRI_SAP_FEEDER_ID", line 6
ORA-04088: error during execution of trigger 'DCMIS.TRI_SAP_FEEDER_ID'
我把触发的时机改成before就不会出错,请问哪位知道为何?
AFTER INSERT
ON DCMIS.AA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_IP VARCHAR2(15);
V_AA INT ;
BEGIN
--SELECT AA_NO INTO V_IP FROM DUAL;
SELECT COUNT(*) INTO v_aa from AA;
IF MOD(V_AA,2) =0 THEN
INSERT INTO FEEDER_SAP(FEEDER_NO,FLAT)
VALUES(:old.A,'N');
END IF;END;出错提示如下
ORA-04091: table DCMIS.AA is mutating, trigger/function may not see it
ORA-06512: at "DCMIS.TRI_SAP_FEEDER_ID", line 6
ORA-04088: error during execution of trigger 'DCMIS.TRI_SAP_FEEDER_ID'
我把触发的时机改成before就不会出错,请问哪位知道为何?
--SELECT AA_NO INTO V_IP FROM DUAL;
SELECT COUNT(*) INTO v_aa from AA;
IF MOD(V_AA,2) =0 THEN
INSERT INTO FEEDER_SAP(FEEDER_NO,FLAT)
VALUES(:old.A,'N');
END IF;
执行语句可以执行?
SELECT COUNT(*) INTO v_aa from AA;
IF MOD(V_AA,2) =0 THEN
INSERT INTO FEEDER_SAP(FEEDER_NO,FLAT)
VALUES(:new.A,'N');
END IF;
把:old改成:new可以执行,但触发器还是提示相同的错误
楼主的代码在aa表上建立触发器,又对aa表进行查询,所以报错
改成before不能解决该问题。insert ... values..不会报错是因为查询操作是在insert之前,此时表尚未发生变化。使用insert into .. select .. from ..
插入1条或多条记录试试,照样报错的可以利用aa表中的字段值来代替这个select操作,性能也要高得多:比如利用id字段,该字段使用序列赋值递增。那么只要判断IF MOD(:new.id,2) =0 THEN
就好了
AFTER INSERT
ON DCMIS.AA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_IP VARCHAR2(15);
V_AA INT ;
BEGIN
--SELECT AA_NO INTO V_IP FROM DUAL;
SELECT COUNT(*) INTO v_aa from AA;
IF MOD(V_AA,2) =0 THEN
INSERT INTO FEEDER_SAP(FEEDER_NO,FLAT)
VALUES(:old.A,'N');
END IF;END;
你这个触发器是,insert以后触发的,因此对与该触发器来讲,应该没有:old.a这样的值,因为你插入数据前在表中根本没有该记录。对于插入的触发器只有:new.a这样的值。
IF MOD(V_AA,2) =0 THEN
INSERT INTO FEEDER_SAP(FEEDER_NO,FLAT)
VALUES(:old.A,'N');
END IF;如果是修改操作的触发器,有:old.x和:new.x这样的值。
如果是删除操作的触发器,有:old.x这样的值。
如果是添加操作的触发器,只有:new.x这样的值。
VALUES(:old.A,'N'); 这个而言。
VALUES(:old.A,'N');
插入之前有值么??
.new.a是有值的,但条件是before
请问三楼
SELECT COUNT(*) INTO v_aa from AA还需要有条件查询的,触发器不能在原表上进行select的动作,是否还有其它的方式可以实现呢?