在oracle触发器中运行多个insert和update语句

Question

我有一个oracle触发器插入数据到另一个表。如果我使用单个更新查询，它会工作，但是如果我使用多个命令，如更新和插入，它会失败，错误接踵而至。

ORA-04091: table ADMIN_SMS_HANDLER is mutating, trigger/function may not see it
ORA-06512: at "ADMIN_SMS_TRIG", line 16
ORA-04088: error during execution of trigger 'ADMIN_SMS_TRIG'

这工作。

CREATE OR REPLACE TRIGGER ADMIN_SMS_TRIG AFTER
INSERT ON ADMIN_SMS_HANDLER
FOR EACH ROW
DECLARE BEGIN
INSERT INTO SMS (
SMSID,
ANUMBER,
BNUMBER,
MSG,
APP
) VALUES (
SMSSEQ.NEXTVAL,
:NEW.SMS_FROM,
:NEW.SMS_TO,
:NEW.SMS_TEXT,
'APP'
);
END;

但这不起作用

CREATE OR REPLACE TRIGGER ADMIN_SMS_TRIG AFTER
INSERT ON ADMIN_SMS_HANDLER
FOR EACH ROW
DECLARE BEGIN
INSERT INTO SMS (
SMSID,
ANUMBER,
BNUMBER,
MSG,
APP
) VALUES (
SMSSEQ.NEXTVAL,
:NEW.SMS_FROM,
:NEW.SMS_TO,
:NEW.SMS_TEXT,
'app'
);
UPDATE ADMIN_SMS_HANDLER
SET
SENT_DATE = SYSDATE,
SENT_STATUS = 1,
UPDATED_BY = 'trigger',
UPDATED_DATE = SYSDATE
WHERE
ID = :NEW.ID;
END;

Answer 1

问题与多条语句无关。问题是行级触发器(通常)不能引用定义它的表。

假设目的是让触发器自动填充一些列，您可以通过直接在:new伪记录中设置值来实现，即

:new.sent_date := sysdate;
:new.sent_status := 1;

但是由于要修改当前行的数据，因此需要使用before insert触发器而不是after insert触发器。就像

CREATE OR REPLACE TRIGGER ADMIN_SMS_TRIG 
BEFORE INSERT ON ADMIN_SMS_HANDLER
FOR EACH ROW
DECLARE 
BEGIN
INSERT INTO SMS (
SMSID,
ANUMBER,
BNUMBER,
MSG,
APP
) VALUES (
SMSSEQ.NEXTVAL,
:NEW.SMS_FROM,
:NEW.SMS_TO,
:NEW.SMS_TEXT,
'app'
);
:new.SENT_DATE := SYSDATE;
:new.SENT_STATUS := 1;
:new.UPDATED_BY := 'trigger';
:new.UPDATED_DATE := SYSDATE;
END;