Oracle Trigger creation - table is mutating; trigger may not read or modify it

Question

As the table is mutating the following trigger does not work as I believe the SQL statement within the trigger cannot be executed against a mutating table, however as I am not on 11g I cannot create a compound trigger. I have tried including PRAGMA AUTONOMOUS TRANSACTION; in the declaration section, however this would not compile. Could anyone provide me with the best solution?

create or replace 
trigger fb_pers_id_check2_tr
  --after insert on ifsapp.person_info_tab
  before insert on ifsapp.person_info_tab      
for each row
begin
  declare
--  pragma autonomous_transaction;
    v_pid_ person_info_tab.person_id%type;
    format_name_ person_info_tab.name%type;
  begin

  v_pid_ := :new.person_id;

  select regexp_replace(upper(:new.name), '\W')
    into format_name_
  from ifsapp.person_info_tab
  where person_id = v_pid_;

  if length(v_pid_) < 3 and (length(format_name_) < 21 and v_pid_ <> format_name_) then
    raise_application_error(-20001, 'Person ID: ' || v_pid_ || 'is not valid, please enter a valid     Person ID, e.g. "' || format_name_ || '".');
  end if;
end;
end fb_pers_id_check2_tr;

N.B. In plain English this trigger is intended to stop users setting a person id that is less than 3 characters long and does not equal variable 'format_name_' if it is less than 21 characters long.

Answer 1

Oracle doesn't allow a row trigger to read or modify the table on which the trigger is defined. However, if PERSON_ID is a PRIMARY or UNIQUE key on PERSON_INFO_TAB (which seems to be the case given that it's used in a singleton SELECT) you don't really need to read the table - just use the OLD or NEW values where appropriate:

create or replace trigger fb_pers_id_check2_tr
  before insert on ifsapp.person_info_tab
  for each row
declare
  v_pid_ person_info_tab.person_id%type;
  format_name_ person_info_tab.name%type;
begin
  v_pid_ := :new.person_id;

  format_name_ := REGEXP_REPLACE(UPPER(:new.name), '\W');    

  if length(v_pid_) < 3 and
     length(format_name_) < 21 and
     v_pid_ <> format_name_
  then
    raise_application_error(-20001, 'Person ID: ' || v_pid_ ||
                            ' is not valid, please enter a valid' ||
                            ' Person ID, e.g. "' || format_name_ || '".');
  end if;
end fb_pers_id_check2_tr;

Here the code is checking the NEW value of NAME (which I think is right, given that it appears to be validating input), but if the intent was to check the OLD value it's simple to change :NEW to :OLD in the REGEXP_REPLACE call.

Share and enjoy.