Auditing from a trigger in oracle

Question

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

Because Mary and Peter modified some of these rows as well, perhaps? — Littlefoot
Without seeing the code that is actually being executed, it is difficult for anyone else to understand what is going on. Can you share some of the code that is generating the audit rows? — EJ Egyed
@Littlefoot no, no rows are specified towards them. Only person who is generating, their name supposed to show up — Basudev Singh
Then remove or update from the trigger. Because, John might be "generating" (inserting) rows, but if Mary or Peter update some of them, their names will be stored in the audit column. — Littlefoot

psaraj12 psaraj12 · Accepted Answer · 2021-04-13T17:34:19

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;

Auditing from a trigger in oracle

1 Answers