[Oracle] audit functions using autonomous transactions

[Oracle] uses autonomous transactions to implement the audit function. In a transaction (outer transaction), one or several autonomous transactions can be defined. An autonomous transaction can be committed independently without affecting the outer transaction. Similarly, the rollback of the outer transaction does not affect the autonomous transaction. If you have such a requirement, you cannot perform certain operations (such as update) on a table. Once such operations are performed, the entire rollback is forced, in addition, this violation operation is recorded in the audit log. Now the problem is that after the transaction is rolled back, the records in the log will also be rolled back, and autonomous transactions can be used to prevent this problem. First, define a log table for audit. [SQL] create table error_logs (id number (10) not null, log_timestamp timestamp not null, error_message varchar2 (4000); create sequence error_log_seq; create a stored procedure for an autonomous transaction (the keyword PRAGMA AUTONOMOUS_TRANSACTION represents an autonomous transaction) to insert error information to the audit table [SQL] create or replace procedure log_errors (p_error_message IN varchar2) as PRAGMA autonomous_trans; BEGIN insert into error_logs (id, log_timestamp, error_message) values (err Or_log_seq.NEXTVAL, systimestamp, p_error_message); commit; END;/create a test table that defines that the ID field of the table is not empty [SQL] create table at_test (id number (10) not null, des varchar2 (200); defines a stored procedure and tries to insert illegal data to the test table [SQL] create or replace procedure p1 as BEGIN insert into at_test (id, des) values (1, 'desc1'); insert into at_test (id, des) values (NULL, 'desc2'); exception when others then log_errors (p_error_message => SQLERRM ); Rollback; END;/run this stored procedure to check whether the audit is successful. [SQL] SQL> exec p1 PL/SQL process has been completed successfully. SQL> select * from at_test; unselected row SQL> select * from error_logs; ID LOG_TIMESTAMP ERROR_MESSAGE ---------- -------------------------------13 03.34.51.210000 afternoon ORA-01400: NULL cannot be inserted ("TEST ". "AT_TEST ". "ID") from the above results, we can find that the transaction that inserts data to the at_test table is rolled back normally, but the audit autonomous transaction is not rolled back.