Oracle Trigger To Check Values On Insert & Update

Question

I am trying to check a value-gap crossed with each other or not. Let me explain with a quick scenario.

Rec 1 : Company AA : Distance Gap -> 100 - 200
Rec 2 : Company AA : Distance Gap -> 200 - 300 VALID
Rec 3 : Company AA : Distance Gap -> 250 - 450 INVALID
Rec 4 : Company JL : Distance Gap -> 250 - 450 VALID

Rec 3 Invalid because it is between REC 2's distance values. Rec 4 Valid because company is different

Thus I wrote a trigger to prevent it.

create or replace trigger ab_redemption_billing_mpr
  after insert or update on redemption_billing_mpr
  for each row
declare
  v_is_distance_range_valid boolean;
begin
  v_is_distance_range_valid := p_redemption_billing.is_distance_range_valid(:new.id,
                                                                            :new.redemption_billing_company,
                                                                            :new.distance_min,
                                                                            :new.distance_max,
                                                                            'redemption_billing_mpr');
  if not v_is_distance_range_valid then
    raise_application_error(-20001, 'This is a custom error');
  end if;
end ab_redemption_billing_mpr;

FUNCTION:

function is_distance_range_valid(p_id           in number,
                                   p_company      in varchar2,
                                   p_distance_min in number,
                                   p_distance_max in number,
                                   p_table_name   in varchar2) return boolean is
    d_record_number number;
  begin
    execute immediate 'select count(*) from ' || p_table_name || ' r 

        where r.redemption_billing_company = :1 
        and (:2 between r.distance_min and r.distance_max or :3 between r.distance_min and r.distance_max)
        and r.id = nvl(:4, id)'
      into d_record_number
      using p_company, p_distance_min, p_distance_max, p_id;
    if (d_record_number > 0) then
      return false;
    else
      return true;
    end if;
  end;

is_distance_range_valid() works just as I expected. If it returns false, that means range check is not valid and don't insert or update.

When I create a scenario to catch this exception, oracle gives

ORA-04091: table name is mutating, trigger/function may not see it

and it points select count(*) line when I click debug button.

I don't know why I am getting this error. Thanks in advance.

Answer 1

Just check whether the below approach resolves your issue

Create a log table with columns required for finding the is_distance_range_valid validation and In your trigger insert into this log tab.The trigger on main table has to be a before insert or update trigger

Create a trigger on this log table and do the validation in the log table trigger and raise error.The trigger on log table has to be an after insert or update trigger.

Also this will only work if the row is already existing in the main table and it is not part of the current insert or update. If validation of the current insert or update is required then we need to use the :new.column_name to do the validation

Test:-

    create table t_trg( n number);

    create table t_trg_log( n number);


    create or replace trigger trg_t
    before insert or update on t_trg
      for each row
    declare
    l_cnt number;
    begin
    insert into t_trg_log values(:new.n);
    end trg_t;

    create or replace trigger trg_t_log
      after insert or update on t_trg_log
      for each row
    declare
    l_cnt number;
    begin
    select count(1) into l_cnt from t_trg
    where n=:new.n;

    if  l_cnt  > 0  then
        raise_application_error(-20001, 'This is a custom error');
      end if;

    end trg_t_log;

The first time I insert it doesn't throw error

  insert into t_trg values(7);

  1 row inserted.

The second time I execute, I get the custom error

   insert into t_trg values(7);

  Error report -
  ORA-20001: This is a custom error
  ORA-06512: at "TRG_T_LOG", line 7
  ORA-04088: error during execution of trigger 'TRG_T_LOG'
  ORA-06512: at "TRG_T", line 4
  ORA-04088: error during execution of trigger 'TRG_T'

Update:-Using compound trigger the error is thrown in the first time the insert is done since it also takes into account the current row that we are updating or inserting while doing the SELECT

Answer 2

First I want to thanks to psaraj12 for his effort.

I found solution. Oracle forces us to not use select query before each row. Oracle trigger error ORA-04091

So I wrote this and it works.

create or replace trigger ab_redemption_billing_mpr
  for insert or update on redemption_billing_mpr
  compound trigger

  v_is_distance_range_valid  boolean;
  id                         redemption_billing_mpr.id%type;
  redemption_billing_company redemption_billing_mpr.redemption_billing_company%type;
  distance_min               redemption_billing_mpr.distance_min%type;
  distance_max               redemption_billing_mpr.distance_max%type;

  after each row is
  begin
    id                         := :new.id;
    redemption_billing_company := :new.redemption_billing_company;
    distance_min               := :new.distance_min;
    distance_max               := :new.distance_max;
  end after each row;

  after statement is
  begin
    v_is_distance_range_valid := p_redemption_billing.is_distance_range_valid(id,
                                                                              redemption_billing_company,
                                                                              distance_min,
                                                                              distance_max,
                                                                              'redemption_billing_mpr');
    if not v_is_distance_range_valid then
      raise_application_error(-20001, 'This is a custom error');
    end if;
  end after statement;

end ab_redemption_billing_mpr;

We must use compound trigger to deal with it.