Need to insert machine name and DML command in table through trigger

Question

I need to create a trigger that can insert in an audit table which DML command- insert update delete has been used on a base table plus with machine name.

Please guide me with this.

Answer 1

Let me show you an example:

1.A table my_test where I will make the dml operations

2.An audit table to record the operations

3.A Trigger to capture the values interested.

SQL> create table my_test ( c1 number, c2 number );

Table created.

SQL> create table audit_my_test ( id_timestamp timestamp, 
                                  session_id number, 
                                  username varchar2(30), 
                                  machine varchar2(50), 
                                  sql_text varchar2(400), 
                                  operation_type varchar2(20) 
                                  );

Table created.

SQL> create or replace trigger my_trg_my_test after insert or delete or update on my_test
 referencing new as new old as old
 for each row
 declare
 v_sql varchar2(400);
 v_usr varchar2(40);
 v_ope varchar2(20);
 v_ter varchar2(50);
 v_sid number;
 begin
            
 select sys_context('USERENV','SESSION_USER'),
        sys_context('USERENV','CURRENT_SQL'),
        sys_context('USERENV','SID'),
        sys_context('USERENV','HOST')
  into 
        v_usr,
        v_sql,
        v_sid,
        v_ter
  from dual;
  
IF INSERTING THEN
v_ope := 'Insert';
ELSIF UPDATING THEN
v_ope := 'Update';
ELSIF DELETING THEN
v_ope := 'Delete';
END IF;

    insert into audit_my_test values ( systimestamp , v_sid, v_usr, v_ter, v_sql, v_ope );
end;
/

Trigger created.

SQL> show err
No errors

Now let's make some DML operations over the table

SQL> insert into my_test values ( 1 , 1) ;

1 row created.

SQL>  insert into my_test values ( 2 , 2) ;

1 row created.

SQL> commit;

Commit complete.

Verify the audit table

ID_TIMESTAMP                                                                SESSION_ID USERNAME                       MACHINE
--------------------------------------------------------------------------- ---------- ------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPERATION_
----------
24-JUL-20 01.01.25.567641 PM                                                       328 SYS                            scglvdoracd0006.scger.dev.corp

Insert

24-JUL-20 01.01.45.514662 PM                                                       328 SYS                            scglvdoracd0006.scger.dev.corp

Insert

ID_TIMESTAMP                                                                SESSION_ID USERNAME                       MACHINE
--------------------------------------------------------------------------- ---------- ------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPERATION_
----------

You can read all the attributes from sys_context here:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

The problem with this solution: You will never get the SQL responsible for the operation. CURRENT_SQL in SYS_CONTEXT only works inside a procedure and when it is invoked in a Fine Grain Access (FGA) policy as a handler.

If you need the SQL responsible for the operation, you either construct a FGA policy and a handler, or you use AUDIT which is much better in this specific case.