CODEWITHSUNDEEP
oracle-databasetriggersoracle-apexoracle19coracle-apex-20.2
Basudev Singh
Basudev Singh

Reputation: 69

Auditing from a trigger in oracle

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

Upvotes: 0

Views: 611

Answers (1)

psaraj12
psaraj12

Reputation: 5072

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;

Upvotes: 1

Related Questions