Accessing the insert statement which triggers a trigger inside the trigger's body

Question

I have some table and I have enabled auditing on this table. Currently, I am collecting the data about who has inserted records and at what time. Now I also want to log what was the exact query he/she used to insert the record. I created a trigger which at present only logs the user and date to audit data collection table. This is code to the trigger:

create or replace trigger auditer
after insert on user01.sometab
for each row
insert into sys.auditlogs values(user,sysdate)

How can I use the same trigger to access the insert statement and insert that statement into the audit table? Is it even possible?

Answer 1

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.

For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried from SYS.AUD$ table if AUDIT_TRAIL value is set to DB,EXTENDED or DB, or read from OS files if AUDIT_TRAIL is set to XML,EXTENDED or XML. In the latter case you can determine where the audit files are located based on the value of the initialization parameter AUDIT_FILE_DEST:

SQL> show parameter audit

NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/oracle/admin/orcl/adump

As far as FGA is concerned, I have already described it in my other post here. You would just need to set the other value for the audit_trail parameter when creating the FGA with DBMS_FGA.ADD_POLICY procedure, e. g. DBMS_FGA.DB_EXTENDED.