SQL & PL/SQL

hi all

I am having a problem where a pl/sql process inserts rows into a table, using the 'user' function to set a couple of columns. My table has audit fields of date_entered, date_modified, who_entered and who_modified to record who and when the last insert / update was done. In case anyone gets lazy and writes a script that forgets to set these fields, there is a trigger on the table that sets them anyway.

The problem is that when the trigger fires, the :new.who_entered and :new.who_modified fields seem to be null, even though the calling pl/sql block is setting them. The inserted rows have nulls in these fields. If I disable the trigger, the user values are set correctly on insert, which indicates to me that the pl/sql block is ok. Something about the trigger or the database setup seems to be preventing the 'user' value to be passed to the trigger. The :new date values (that are populated from sysdate) are fine.

What is really odd is that if I change the pl/sql block to concatenate any character to the user function (i.e. user||'a'), that value is passed through to the trigger and ends up in the column.


The pl/sql block looks like -

declare
begin
for i in 1..10 loop
insert into company_label (
label_id ,description
,date_entered, date_modified, who_entered, who_modified )
values (
i,'description '||to_char(i)
,sysdate,sysdate,user,user);
end loop;
commit;
end;
/

The trigger looks like
create or replace trigger company_label_audit
before insert or update on company_label
for each row
begin
if inserting and :new.date_entered is null then
:new.who_entered:= user;
:new.date_entered:= sysdate;
:new.who_modified:= user;
:new.date_modified:= sysdate;
elsif updating
and ((:new.date_modified is null or :old.date_modified = :new.date_modified)
and user <> 'AIMS') then
:new.who_modified:= user;
:new.date_modified:= sysdate;
end if;
end;


thanks
Martina