我有触发审计,它存储了对任何EMP表行执行的操作.
这个触发器工作正常,除了在某些情况下(很少发生,我无法确定确切的条件)它给了我
Oracle错误：ORA-01400：无法插入NULL(“MY_SCHEMA”.“HIST_EMP”.“操作”)
CREATE OR REPLACE TRIGGER HIST_EMP_AIUD
AFTER UPDATE OR INSERT OR DELETE
ON EMP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_action VARCHAR2(1) := 'D';
BEGIN
IF INSERTING THEN
v_action := 'A';
ELSIF UPDATING THEN
v_action := 'U';
END IF;
IF DELETING THEN
INSERT INTO hist_emp (source_rowid,source_date,action)
VALUES (:old.rowid,SYSDATE,v_action);
ELSIF INSERTING OR UPDATING THEN
INSERT INTO hist_emp (source_rowid,action)
VALUES (:new.rowid,v_action);
END IF;
EXCEPTION
WHEN OTHERS THEN
--Code to Log
-- <some exception handling should be placed here >
END;
解决方法
我想,变量v_action初始化会发生一些奇怪的事情.尝试处理所有3种可能性：
v_action:= null;
IF INSERTING THEN
v_action := 'A';
ELSIF UPDATING THEN
v_action := 'U';
ELSIF DELETING THEN
v_action := 'D';
END IF;
