我有表如下:
CREATE TABLE ETLMSGASSGNMNTTEST
(
MSGID INTEGER NOT NULL,
TIMEFROM DATE NOT NULL,
ASSGNMNTTYPECODE INTEGER NOT NULL,
REQUESTID INTEGER NOT NULL,
TIMETO DATE NOT NULL,
QUEUEID INTEGER NOT NULL,
AGENTID INTEGER,
SEGMENTID INTEGER NOT NULL,
REASONCODE NVARCHAR2(3) NOT NULL,
UPDATEUSERID INTEGER,
ASSGNMNTCOMMENT NVARCHAR2(254),
CONCATFROMANDTO VARCHAR2(32 BYTE)
)在次表基础上,我建立有这个trigger:
CREATE OR REPLACE TRIGGER DBADMIN.MSGASSGNMNTTEST_IUT
AFTER INSERT OR UPDATE
ON DBADMIN.ETLMSGASSGNMNTTEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
update ETLMsgAssgnmntTest
set concatFromAndTo = TO_CHAR(:NEW.TimeFrom,'YYYYMMDDHH24MISS')||TO_CHAR(:NEW.TimeTo,'YYYYMMDDHH24MISS')
where rowid = :new.rowid;
END MSGASSGNMNTTEST_IUT;
建好该trigger后,用如下语句插入数据:
insert into ETLMSGASSGNMNTTEST
(MSGID,TIMEFROM,ASSGNMNTTYPECODE,REQUESTID,TIMETO,QUEUEID,AGENTID,SEGMENTID,REASONCODE,UPDATEUSERID,ASSGNMNTCOMMENT)
values
(1, sysdate, 1, 1, sysdate, 1, 1, 1, 'y',1,'xxxx');结果得到如下错误:
ORA-04091: table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.Action: Rewrite the trigger (or function) so it does not read that table.
请问我到底出了什么问题?
CREATE TABLE ETLMSGASSGNMNTTEST
(
MSGID INTEGER NOT NULL,
TIMEFROM DATE NOT NULL,
ASSGNMNTTYPECODE INTEGER NOT NULL,
REQUESTID INTEGER NOT NULL,
TIMETO DATE NOT NULL,
QUEUEID INTEGER NOT NULL,
AGENTID INTEGER,
SEGMENTID INTEGER NOT NULL,
REASONCODE NVARCHAR2(3) NOT NULL,
UPDATEUSERID INTEGER,
ASSGNMNTCOMMENT NVARCHAR2(254),
CONCATFROMANDTO VARCHAR2(32 BYTE)
)在次表基础上,我建立有这个trigger:
CREATE OR REPLACE TRIGGER DBADMIN.MSGASSGNMNTTEST_IUT
AFTER INSERT OR UPDATE
ON DBADMIN.ETLMSGASSGNMNTTEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
update ETLMsgAssgnmntTest
set concatFromAndTo = TO_CHAR(:NEW.TimeFrom,'YYYYMMDDHH24MISS')||TO_CHAR(:NEW.TimeTo,'YYYYMMDDHH24MISS')
where rowid = :new.rowid;
END MSGASSGNMNTTEST_IUT;
建好该trigger后,用如下语句插入数据:
insert into ETLMSGASSGNMNTTEST
(MSGID,TIMEFROM,ASSGNMNTTYPECODE,REQUESTID,TIMETO,QUEUEID,AGENTID,SEGMENTID,REASONCODE,UPDATEUSERID,ASSGNMNTCOMMENT)
values
(1, sysdate, 1, 1, sysdate, 1, 1, 1, 'y',1,'xxxx');结果得到如下错误:
ORA-04091: table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.Action: Rewrite the trigger (or function) so it does not read that table.
请问我到底出了什么问题?
AFTER INSERT OR UPDATE
ON DBADMIN.ETLMSGASSGNMNTTEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:new.concatFromAndTo := TO_CHAR(:NEW.TimeFrom,'YYYYMMDDHH24MISS')||TO_CHAR(:NEW.TimeTo,'YYYYMMDDHH24MISS');
END MSGASSGNMNTTEST_IUT;
AFTER INSERT OR UPDATE
ON DBADMIN.ETLMsgAssgnmntTest2
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_datefrom DATE;
v_dateto DATE;
v_id INTEGER;
BEGIN
v_datefrom := :NEW.timefrom;
v_dateto := :NEW.timeto;
v_id := :NEW.MSGID; UPDATE etlmsgassgnmnttest2
SET concatfromandto =
( TO_CHAR (v_datefrom, 'YYYYMMDDHH24MISS')
|| TO_CHAR (v_dateto, 'YYYYMMDDHH24MISS')
)
WHERE timefrom = v_datefrom AND timeto = v_dateto;
END MSGASSGNMNTTEST2_IUT;