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
p_time := systimestamp;
then p_type := 'I';
then p_type := 'D';
else p_type := 'U';
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)
insert into SYS_AUDITS(ID,AUDIT_COLUMNS_ID,USER_ID,OP_TYPE,OP_DATE,INITIAL_VALUE,LAST_VALUE,ID1,ID2, USER_NAME )