[Oracle] audit functions using autonomous transactions

One or several autonomous transactions can be defined in a transaction (outer transaction. An autonomous transaction can be committed independently without affecting the outer transaction.

One or several autonomous transactions can be defined in a transaction (outer transaction. An autonomous transaction can be committed independently without affecting the outer transaction.

One or several autonomous transactions can be defined in a transaction (outer transaction. 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.

Create table error_logs (
Id number (10) not null,
Log_timestamp timestamp not null,
Error_message varchar2 (4000)
);

Create sequence error_log_seq;

Creates a stored procedure for an autonomous transaction (the keyword PRAGMA AUTONOMOUS_TRANSACTION represents an autonomous transaction), used to insert error information to the audit table.

Create or replace procedure log_errors (p_error_message IN varchar2)
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert into error_logs (id, log_timestamp, error_message)
Values (error_log_seq.NEXTVAL, systimestamp, p_error_message );
Commit;
END;
/

Create a test table. The ID field of the table is not empty.

Create table at_test (
Id number (10) not null,
Des varchar2 (200)
);

Define a stored procedure and try to insert invalid data into the test table

Create or replace procedure p1
BEGIN
Insert into at_test (id, des)
Values (1, 'sc1 ');
Insert into at_test (id, des)
Values (NULL, 'desc2 ');
EXCEPTION
WHEN OTHERS THEN
Log_errors (p_error_message => SQLERRM );
Rollback;
END;
/

Run the stored procedure to check whether the audit is successful.

SQL> exec p1

The PL/SQL process is successfully completed.

SQL> select * from at_test;

Unselected row

SQL> select * from error_logs;

ID LOG_TIMESTAMP ERROR_MESSAGE
-------------------------------------------------------------------------------------------------
ORA-01400: cannot insert NULL ("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.