3 Replies Latest reply: Mar 28, 2013 7:54 AM by Sc0tt RSS

    APEX Audit Issue

    983543
      I have created a trigger for Audit purpose on a table. Below is the trigger code. My requirement is whenevr there is a change to the record in the Tracking page, a row will be inserted into Audit table.

      I have created GLOBALVARS package and inclused the variables GUSID, GUSER,GROLE.

      This error is coming when deleting the record from the Application.

      This is working fine when a new record is created in the Tracking page and whenever the record is modified in Tracking page.

      But the issue is when deleting the particular record. I am getting "ORA-01400: cannot insert NULL into ("APP"."AUDIT"."USER_ID") ORA-06512: at "APP.AUDIT_TRG_PLAN", line 17 ORA-04088: error during execution of trigger 'APP.AUDIT_TRG_PLAN'


      create or replace TRIGGER AUDIT_TRG_PLAN
      AFTER INSERT OR DELETE OR UPDATE
      OF NAME ON PLAN REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
      DECLARE
      p_time timestamp;
      p_type char(1);
      BEGIN
      p_time := systimestamp;
      if INSERTING
      then p_type := 'I';
      elsif DELETING
      then p_type := 'D';
      else p_type := 'U';
      end if;
      if (:OLD.NAME is not null and :NEW.NAME is not null
      and :OLD.NAME != :NEW.NAME)
      or (:OLD.NAME is not null and :NEW.NAME is null)
      or (:OLD.NAME is null and :NEW.NAME is not null)
      then
      insert into SYS_AUDITS(ID,AUDIT_COLUMNS_ID,USER_ID,OP_TYPE,OP_DATE,INITIAL_VALUE,LAST_VALUE,ID1,ID2, USER_NAME )
      values (IDSEQ.nextval,21124,nvl(GLOBALVARS.GUSID,GLOBALVARS.GUSID),p_type,p_time,:OLD.NAME,:NEW.NAME,:NEW.ID,NULL,GLOBALVARS.GUSER);
      end if;
      end;
      /

      Please provide me some solution for this issue.

      Edited by: 980540 on Mar 28, 2013 4:12 AM