Mutating Table - autonomous transaction problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Mutating Table - autonomous transaction problem

  1. #1
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    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
    Last edited by buck; 02-22-2005 at 05:04 PM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ==
    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

  3. #3
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    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)
    Last edited by buck; 02-23-2005 at 02:16 AM.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    thanks!

    Hi,

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

    thanks again...

    Stefan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width