Mutating Table - autonomous transaction problem

[buck]

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

[tamilselvan]

==
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

[buck]

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)

[jmodic]

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:

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;

[buck]

Hi,

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

thanks again...

Stefan