guys:

I wonder if there is a way to write a trigger in Oracle to do both things: saving data to a log table and raising a user defined exception as well?

I am trying to figure out a strange error on my team's database, which causes data inconsistency per business logic. Multiple team's application can access this database. So I wrote a trigger to monitor certain column in a table which causes the problem. I want to save data such as user ID, saving time etc. to a log table if value is incorrect, but I also want to raise exception to attract attention. However, whenever my trigger raises the user defined exception, saving data to log table is not finished. Can anyone give a suggestion about it? Thank you in advance.

CodePudding user response：

You can write a logging function that uses an autonomous transaction

create or replace procedure log_autonomous( p_log_message in varchar2,
                                            p_other_parameters... )
as
  pragma autonomous_transaction;
begin
  insert into log_table ...

  commit;
end;

and then call that logging function from your trigger

create or replace trigger my_trigger
  before insert or update on some_table
  for each row
declare
begin
  if( some_bad_thing )
  then
    log_autonomous( 'Some message', ... );
    raise_application_error( -20001, 'Some error' );
  end if;
end;

The log_table message will be preserved because it was inserted in a separate (autonomous) transaction. The triggering transaction will be rolled back because the trigger raises an exception.