Using autonomous transactions to implement audit functions in Oracle

Source: Internet
Author: User
Tags commit rollback

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/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.