Auditing an Oracle constraint violation?

Question

Can I audit an Oracle constraint violation?

I created a constraint in a table and I want to audit it when some process violates this constraint. Is this possible? How can I do it?

I'm using Oracle 11g.

Answer 1

As one of the options you could create a schema level trigger to catch database errors. Here is an example:

-- our test table
SQL> create table TB_Test(
  2    col1 number primary key,
  3    col2 number,
  4    col3 number,
  5    constraint CHK_CONS check (col3 < 5)
  6  )
  7  ;

 -- table we going to log errors into   
SQL> create table Db_Errors(
  2    msg varchar2(1000)
  3  )
  4  ;

Table created

-- schema level trigger, which fires when 
-- exception of certain code is raised.
-- In this case, we filter exceptions' codes out in the when clause 
-- of the trigger.
-- 1     - unique constraint violation
-- 2290  - check constraint violation
-- 2292  - foreign key constraint violation
-- you can add more
SQL> create or replace trigger TR_CatchErrors
  2  after servererror on schema
  3  when (ora_server_error(1) in (1, 2290, 2292))
  4  begin
  5    insert into Db_Errors(Msg)
  6      values(ora_server_error_msg(1));
  7  end;
  8  /

Trigger created

-- Test case;
-- 1  check constraint violation 
SQL> insert into tb_test(col1, col2, col3)
  2    values(1, 2, 6);

insert into tb_test(col1, col2, col3)
  values(1, 2, 6)

ORA-02290: check constraint (HR.CHK_CONS) violated

-- unique constraint violation
SQL> insert into tb_test(col1, col2, col3)
  2    values(1, 2, 4);

insert into tb_test(col1, col2, col3)
  values(1, 2, 4)

ORA-00001: unique constraint (HR.SYS_C0014608) violated

-- And here what we have in our DB_Errors table logged
SQL> select msg 
  2    from db_errors;

MSG
--------------------------------------------------------------------------------
ORA-02290: check constraint (HR.CHK_CONS) violated
ORA-00001: unique constraint (HR.SYS_C0014608) violated

As another option(starting from 10gR2), you can use log errors clause of a DML statement. But it would require you to provide log errors clause for each dml statement. Here is an example:

-- create a table for error logging
-- using create_error_log() procedure of dbms_errlog package
-- for a specific table. You could also create your own custom 
-- error logging table

begin
  dbms_errlog.create_error_log('TB_TEST');
end;

-- unique constraint violation
SQL> insert into tb_test(col1, col2, col3)
  2    values(1, 2, 3)
  3  log errors reject limit unlimited
  4  ;

0 rows inserted


SQL> select ora_err_mesg$
  2    from err$_tb_test t
  3  ;

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-00001: unique constraint (HR.SYS_C0014608) violated