在某些列中使用触发器更新旧值的行

Question

当我在表更新上使用触发器(Oracle v11和v12)时，我希望每行的两列包含旧值。使用的触发器如下:

SET DEFINE OFF;
CREATE OR REPLACE TRIGGER X_Exclusion_Sequence_Field3
AFTER UPDATE ON CATGPENREL
FOR EACH ROW
DECLARE
old_sequence NUMBER;
old_field3 NUMBER(20,5);
BEGIN
if (:OLD.SEQUENCE <> :NEW.SEQUENCE OR :OLD.FIELD3 <> :NEW.FIELD3) then
-- backup
select SEQUENCE, FIELD3 into old_sequence, old_field3 from CATGPENREL where CATGROUP_ID = 
:NEW.CATGROUP_ID and CATALOG_ID = :NEW.CATALOG_ID and CATENTRY_ID = :NEW.CATENTRY_ID;

update CATGPENREL 
set SEQUENCE = old_sequence, FIELD3 = old_field3 
where CATGROUP_ID = :NEW.CATGROUP_ID and CATALOG_ID = :NEW.CATALOG_ID and CATENTRY_ID = :NEW.CATENTRY_ID;
end if;
END;
/

当我更新时，我得到以下错误:

update CATGPENREL set sequence = 1, LASTUPDATE = sysdate where CATGROUP_ID = 3074457345616688681 and CATALOG_ID = 3074457345616684418 and CATENTRY_ID = 3074457345616734707
Report error -
ORA-04091: The ORAWCSI.CATGPENREL table is being edited, the trigger / function cannot read it
ORA-06512: a "ORAWCSI.X_EXCLUSION_SEQUENCE_FIELD3", line 7
ORA-04088: error while executing the trigger 'ORAWCSI.X_EXCLUSION_SEQUENCE_FIELD3'

你能帮助我吗?

谢谢你。

Answer 1

除非我完全误解了，否则我认为你想要做的是:

CREATE OR REPLACE TRIGGER X_Exclusion_Sequence_Field3
BEFORE UPDATE ON CATGPENREL
FOR EACH ROW
BEGIN
if (:OLD.SEQUENCE <> :NEW.SEQUENCE OR :OLD.FIELD3 <> :NEW.FIELD3) then
:NEW.sequence := :OLD.sequence;
:NEW.field3 = :OLD.field3;
end if;
END;

Answer 2

如果你不想在任何情况下改变值，那么下面的代码可以工作。

CREATE OR REPLACE TRIGGER X_Exclusion_Sequence_Field3
BEFORE UPDATE ON CATGPENREL
FOR EACH ROW
BEGIN
:NEW.sequence := :OLD.sequence;
:NEW.field3 = :OLD.field3;
END;

如果您也需要条件，那么您也需要处理null情况，假设SEQUENCE和FIELD3是数字数据类型

CREATE OR REPLACE TRIGGER X_Exclusion_Sequence_Field3
BEFORE UPDATE ON CATGPENREL
FOR EACH ROW
BEGIN
if (NVL(:OLD.SEQUENCE,-999) <> NVL(:NEW.SEQUENCE,-999) OR NVL(:OLD.FIELD3,-999) <> NVL(:NEW.FIELD3,-999)) then
:NEW.sequence := :OLD.sequence;
:NEW.field3 = :OLD.field3;
end if;
END;