You may notice that while logged in as the BIOTICS_DLINK user and running SQL statements that modify data, the REC_LAST_MOD_USER value is automatically set to BIOTICS_DLINK, regardless of whether you state differently or not in the update/insert statement.  To turn this setting off, add an entry to the HDMS_AUDIT_USER_ID table.


First check if a BIOTICS_DLINK row already exists in the table:

select * from HDMS_AUDIT_USER_ID where oracle_user='BIOTICS_DLINK'


If a row already exists and you are still seeing REC_LAST_MOD_USER value set to BIOTICS_DLINK, you may be logging in as a different user to run your SQL updates, which means you need a row for that user instead of BIOTICS_DLINK. If you are indeed logging in as BIOTICS_DLINK, try deleting the row (using the delete statement below) and adding it again.


If a row does not exist, insert it with this SQL:

INSERT INTO HDMS_AUDIT_USER_ID (HDMS_AUDIT_USER_ID, HDMS_GUI, DISABLE_AUDIT, ORACLE_USER) VALUES (

(SELECT NVL(MAX(HDMS_AUDIT_USER_ID)+1, 1) FROM HDMS_AUDIT_USER_ID),'Y','N','BIOTICS_DLINK');

commit;

The key is the HDMS_GUI value. If set to Y, the application (known as the HDMS GUI when these triggers and tables were first created) triggers use the user provided by the SQL rather than the logged in user. 


Note that this setting requires that REC_LAST_MOD_USER be explicitly set in all SQL statements that modify data. If an insert or update query fails to set REC_LAST_MOD_USER, the query will fail with a cryptic error message, which will occur because the _i and _au triggers will attempt to add an entry to the audit_log table with a null username.


If you no longer want to manually set the REC_LAST_MOD_USER value (and want it set to BIOTICS_DLINK instead), delete the hdms_audit_user_id entry using this SQL:


DELETE from HDMS_AUDIT_USER_ID WHERE ORACLE_USER='BIOTICS_DLINK';

commit;