Hi
In our application we use Auditing to monitor the changes in transaction table. We create a HIST table for respective tran table and trigger would insert data into the HIST table.All the operations are done from the Application.
Example :
CREATE TABLE TBL_TRAN(ID NUMBER,NAME VARCHAR2(100),TRAN_DESC VARCHAR2(200),LST_UPDT_DT DATE,LST_UPDT_BY VARCHAR2(20));
CREATE TABLE TBL_TRAN_HIST(VERSIONID NUMBER,OPERATION VARCHAR2(20),ID NUMBER,NAME VARCHAR2(100),TRAN_DESC VARCHAR2(200),LST_UPDT_DT DATE,LST_UPDT_BY VARCHAR2(20));
insert into TBL_TRAN values (1,'Govind','In India',SYSDATE,'1092');
insert into TBL_TRAN_HIST values (1,'Add',1,'Govind','In India',SYSDATE,'1092');
update TBL_TRAN set TRAN_DESC='In USA' where id=1;
insert into TBL_TRAN_HIST values (2,'Modify',1,'Govind','In USA',SYSDATE,'1092');
Audit output:
User 1092 has changed TRAN_DESC from 'In India' to 'In USA' for Id=1
Note: The User-1092 has Added the first record and MOdified the second record ( for same id=1 )
In the Hist table we use Verion_id column to uniquely identify each Id in the Master table.For the Audit purpose we identify the differces between the old and new version and display those changes.
But the concern is during the Delete process (Hard delete) the trigger does not capture the USER who has done the delete operation (:new doesn't work) due to which we are not able to show in Audit.
Could someone pls help me how to get the USER id for delete operation by any means?
Something like below which may not work straight forward.
1)Should we ask app team to insert the logged user into a table ? (Many users login to the applciation not sure if useful)
2)Ask app team to assign the USER to a global package variable ?
( We are able to achieve this if we are using Soft delete, which cause performance problems once volumes increases )