I am working with a big database and it had many generators of data basically have alot of data being inserted and updated per day. I have a trigger that updates each row every time there is an update or insert and i use the following code to input the person's name from the apex application (the user from apex)
NVL(v('APP_USER'),USER)
My problem comes when there is heavy data entry, for example 500,000 records are being generated by one person (John) and when john generated this data, each row is audited but as john generated more than one person who are users in the apex application shows up in the audit.
So scenario is that john clicks a button to generate data and in the audit fields, more than one users name show up (Mary, John, Peter)
Does anybody have any idea why this is happening?
the entire code, it is very generic
TRIGGER trg_tableA before insert or update
on tableA REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
:new.insert_date:=sysdate;
:new.inserted_by:= nvl(V('APP_USER'),USER);
:new.modified_date:=sysdate;
:new.modified_by:= nvl(V('APP_USER'),USER);
end trg_tableA;
Thank you in advance
As per this link Use v('APP_USER') as default value for column in Oracle Apex There are other options than V('APP_USER'). Since Apex 5, the APP_USER is stored in the sys_context and that is a lot more performant than the V() function. It is available as SYS_CONTEXT('APEX$SESSION','APP_USER').
Please try the below and see if your issue is getting resolved.
TRIGGER trg_tableA before insert or update
on tableA REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
:new.insert_date:=sysdate;
:new.inserted_by:= nvl(sys_context('APEX$SESSION','APP_USER'),user);
:new.modified_date:=sysdate;
:new.modified_by:= nvl(sys_context('APEX$SESSION','APP_USER'),user);
end trg_tableA;