Oracle SQL ORA-24344 使用触发器时出现编译错误的成功

Question

请帮助以下代码的任何错误？

遇到错误代码ORA-24344

CREATE OR REPLACE TRIGGER Table_TRG
AFTER INSERT OR UPDATE
ON Table_A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
BEGIN 
IF (:NEW.EQP_ID <> :OLD.EQP_ID AND :NEW.OP_ID <> :OLD.OP_ID)THEN
INSERT INTO Table_B
(EQP_ID, OP_ID, SV_RECORD, UPDATE_TIME)
VALUES
(:NEW.EQP_ID, :NEW.OP_ID, :NEW.SV_RECORD, :NEW.UPDATE_TIME);
ELSE
UPDATE INTO Table_B
SET :OLD.SV_RECORD = :NEW.SV_RECORD,
:OLD.EQP_ID = :NEW.EQP_ID,
:OLD.OP_ID = :NEW.OP_ID,
:OLD.UPDATE_TIME = :NEW.SYSTIMESTAMP;
END IF;
END;

提前谢谢。

Answer 1

更新语句中有语法错误。此外，REFERENCING NEW AS NEW OLD AS OLD是不必要的。

在您的更新语句中，op_id和eqp_id不应该where clause而不是SET吗？

CREATE OR REPLACE TRIGGER table_trg AFTER
INSERT OR UPDATE ON table_a
FOR EACH ROW
BEGIN 
IF (:new.eqp_id <>:old.eqp_id AND :new.op_id <>:old.op_id ) THEN
INSERT INTO table_b (
eqp_id,
op_id,
sv_record,
update_time
) VALUES (
:new.eqp_id,
:new.op_id,
:new.sv_record,
:new.update_time
);
ELSE 
UPDATE table_b
SET sv_record = :new.sv_record,
update_time  = systimestamp
WHERE eqp_id    = :new.eqp_id   --change the columns in set and where appropriately.
AND op_id     = :new.op_id,
END IF;
end;
/