I am trying to insert into a table that has a trigger on insert after each row through a SQL script. The trigger is expecting a value to insert a row in a log table. I don't know how can I pass this value to let that line execute.
Originally posted by marist89
What type of error are you getting?
Maybe you could post the code in the trigger that is failing so we can look at it?
This is the errors I am getting;
ORA-01400: cannot insert NULL into ("SFTESTUSER"."AUDITLOG"."USERID")
at "SFTESTUSER.ENCP_ADDTOAUDITLOG", line 111
"SFTESTUSER.T1IT_RESUNAVA", line 12
ORA-04088: error during execution of trigger
and this is the trigger code,
create or replace trigger T1IT_RESUNAVA
after insert on resunava
for each row
-- local variables here
select ACTIVELOG into FActiveLog from DEFAULTS;
if FActiveLog = '1' then
--select os_utils.get_os_username into UserName from dual;
s := T1F_UnavaStr('',:new.datefrom,:new.dateto,ENCF_MIL TODIS(:new.timefrom),ENCF_MILTODIS(:new.Timeto),:n ew.reasoncode,:new.reason);
ENCP_ADDTOAUDITLOG('RUADD','Resource unavailability "'||s||'" for resource %RESKEY'||:NEW.KEY_RESOUR|| ' was added.',DateFuncs.GetUserID);
Actually, I think the problem lies with the SFTESTUSER.AUDITLOG table. I believe the USERID column has a not null constraint. You can verify this by either doing a:
and looking at the output for a NOT NULL COLUMN
SQL> desc xyz
Name Null? Type
----------------------- -------- ----------------
X NOT NULL VARCHAR2(10)
Z NOT NULL DATE
In this case, my X and Z columns have NOT NULL constraints and my Y column does not.
You can also verify this through user_tab_columns:
SQL> select column_name, nullable
2 from user_tab_columns
3 where table_name = 'XYZ'
Check that out, that's probably your problem.
If this were my application [...opinion deleted for fear of flames...]