Using autonomous transactions to implement audit functions in Oracle

You can define one or several autonomous transactions in a transaction (outer transaction). Autonomous transactions can be commit independently and do not have an impact on external affairs, and the rollback of the same outer transaction has no effect on autonomous affairs.

Suppose there is a requirement that you cannot perform certain actions on a table (such as updates), force the entire rollback if there is such an operation, and record the violation in the audit log. The problem now is that when transactions are rolled back, records in the log are also rolled back, and autonomous transactions can be used to prevent this problem.

First, define the log table for auditing.

CREATE TABLE error_logs (  
  ID number () not  null,  
  log_timestamp  timestamp not  null,  
  error_ Message  VARCHAR2 (4000)  
);  
      
Create sequence error_log_seq;

Create a stored procedure for an autonomous transaction (keyword pragma autonomous_transaction represents an autonomous transaction) for inserting an error message into an audit table

Create or 

Replace procedure log_errors (P_error_message in varchar2) as
  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 that defines the ID field of the table as NOT null

CREATE TABLE at_test (  
  ID number () NOT NULL,  
  des varchar2  
);

To define a stored procedure that attempts to insert illegal data into a test table

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 =>);  
    Rollback;  
End;  
/

Execute this stored procedure to see if the audit was successful

The sql> exec P1  
      
pl/sql process has been successfully completed.  
      
sql> SELECT * from At_test;  
      
No rows selected  
      
sql> select * from Error_logs;  
      
        ID log_timestamp                    error_message  
---------------------------------------------------------------------- --------

-------------------  
         2 2 August-May-13 03.34.51.210000 pm   ORA-01400: Cannot insert NULL 

("TEST".) At_test "." ID ")

From the results above, it is found that the transaction that inserts data into the At_test table is rolled back normally, while audit autonomy transactions are not rolled back.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/