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.
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.