Mutating Table - autonomous transaction problem

Hi,

i'm trying to avoid a mutating table issue with an autonomous transaction.

i have to tables:

Table A:

Resnr=Reservation #
Pernr=Personal #

ID RESNR PERNR
----------- ----------- ---------
818 358 5161
819 359 5156

Table B:

RESNR PERNR
----------- ---------
358
359

What i need to do, is to ensure that PERNR 5161 is not allowed to be entered into Table B, Column PERNR by RESNR 358.

Table A is a Reservation Table and i need to guarantee that the person who makes the reservation is a different one, that the one that will be entered into Table B.

I need to have a before - row-level trigger. This trigger produces a mutating Table problem, so i've tried to call a autonomous function. I'm not sure, whether that's the best way.

Here are my attempts:
---------------------------------
Trigger:

create or replace trigger trig_check_pernr
before update of pernr on Table B
for each row
declare
v_funct number;

begin
select func_vier_augen(:new.pernr) into v_funct from dual;
.
.
.
end;
---------------------------------------
Function:


create or replace function func_vier_augen(pernr_in number)
pragma autonomous_transaction;
return number is

c_number number;
c_number1 number;
c_number2 number;


begin

select ausleihe.pernr
into c_number
from reservierung, ausleihe
where (pernr_in = reservierung.pernr
and ausleihe.resnr = reservierung.resnr);
return c_number;
commit;
end;
--------------------------------------

Any ideas?

thanks in advance,
Stefan Hausknecht

==
What i need to do, is to ensure that PERNR 5161 is not allowed to be entered into Table B, Column PERNR by RESNR 358
==
If 5161 is not allowed for 358, waht value is allowed?
Why is the column in Table B?

Tamil

Hi,

the value 5161 is allowed in Table B, but not for the RESNR 358, because the PERSNR 5161 in TABLE A is the PERSON who will rent a CAR and the PERNR in TABLE B is our Staff Member.

These two People shouldn't be the same.

So that means, for the RESNR 358 in TABLE B is anything allowed except PERNR 5161. (for instance PERNR 5156)

There is no reason for mutating table error, because you don't need your TABLE_B to be involved in trigger's query at all. All you need to check is your table A. So the trigger should be something like:

Code:

---

create or replace trigger trig_check_pernr
before update of pernr on Table_B
for each row
declare
  v_dummy varchar2(1);
begin
  select null into v_dummy from Table_A
  where resnr = :NEW.resnr
    and pernr = :NEW.pernr;
    and rownum = 1;
  raise_application_error(-20001, 'Not allowed');
exception
  when no_data_found then null;
end;

---

Hi,

wow brilliant! Some things are so easy if you know what to do :)

thanks again...

Stefan