Trigger not able to initialise variable

Question

I have trigger for auditing, which stored the action performed on any row of EMP table.

This trigger works fine, except in some cases (which occurs very rarely, and I cannot identify exact condition) it gives me

Oracle Error: ORA-01400: cannot insert NULL into ("MY_SCHEMA"."HIST_EMP"."ACTION")

 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, source_date, action)
                    VALUES (:new.rowid, SYSDATE, v_action);
      END IF;
EXCEPTION
     WHEN OTHERS THEN
        --Code to Log
        -- <some exception handling should be placed here >

END;

This generally happens when I am deleting the row, but I am not sure. Any thought on why this will be happening? The code looks ok to me...

Wow, that is strange. Code looks OK. Is there actually any code in the "exception when others" clause? I hope so. can you show it? — OldProgrammer, Jun 28 '13 at 05:03
No triggers on `hist_emp`. In When OTHERS section there is an generics procedure call which ultimately makes a insert in a exception_logging table. — Aditya Jain, Jun 28 '13 at 07:57

score 1 · Answer 1 · edited Feb 06 '14 at 07:11

1

Something weird is going on with variable v_action initialization, I guess. Try handling all 3 possibilities:

      v_action:= null;
      IF INSERTING THEN
           v_action := 'A';
      ELSIF UPDATING THEN
           v_action := 'U';
      ELSIF DELETING THEN
           v_action := 'D';
      END IF;

edited Feb 06 '14 at 07:11

shinobi92

127
2
16

answered Jun 29 '13 at 18:07

David Jashi

4,490
1
21
26

@Aditya , Yeah, default v_action VARCHAR2(1) := 'D' is not working in some cases. Try as David suggested... – pahariayogi Feb 15 '14 at 15:46

Trigger not able to initialise variable

1 Answers1