Results 1 to 4 of 4

Thread: Trigger problem

  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: Trigger problem

    here is my room table and data-
    create table room(
    room_no integer not null,
    catagori varchar(20) not null,
    quality varchar(20) not null,
    price integer not null,
    primary key(room_no)
    );

    set serveroutput on;
    create or replace procedure room_av(
    roomno room.room_no%type,
    catagoris room.catagori%type,
    qua room.quality%type,
    pri room.price%type) is
    begin
    insert into room (room_no,catagori,quality,price) values (roomno,catagoris,qua,pri);
    commit;
    end room_av;
    /
    begin
    room_av(102,'single','2nd_class',200);
    room_av(103,'single','2nd_class',200);
    room_av(104,'single','2nd_class',200);
    room_av(105,'single','2nd_class',200);
    room_av(202,'single','1st_class',300);
    room_av(203,'single','1st_class',300);
    room_av(204,'single','1st_class',300);
    room_av(205,'single','1st_class',300);
    room_av(302,'double','2nd_class',250);
    room_av(303,'double','2nd_class',250);
    room_av(304,'double','2nd_class',250);
    room_av(305,'double','2nd_class',250);
    room_av(402,'double','1st_class',400);
    room_av(403,'double','1st_class',400);
    room_av(404,'double','1st_class',400);
    room_av(405,'double','1st_class',400);
    room_av(406,'double','1st_class',400);


    end;
    /

    here is my customer_info table
    drop table customer_info;

    create table customer_info(
    first_name varchar(20) not null,
    last_name varchar(20) not null,
    room_no integer not null,
    catagori varchar(20) not null,
    quality varchar(20) not null,
    price integer not null,
    date_open date not null,
    primary key(room_no)
    );

    here is my trigger

    create or replace trigger cust_room
    before INSERT ON customer_info
    FOR EACH ROW


    BEGIN
    insert into customer_info(catagori,quality,price) select catagori,quality,price from room where room_no=:new.room_no;
    END cust_room;
    /

    the trigger is created successfully but when i am executing the command
    insert into customer_info (first_name,last_name,room_no,date_open) values ('Tanmoy','Saha','202','21-jul-2010');
    then mutating error occur
    how can i solve this problem???

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Interesting. I would expect it fails on endless loop, as in BEFORE INSERT trigger you INSERT to the same table, which invokes BEFORE INSERT trigger which INSERTs to the same table, which invokes BEFORE INSERT trigger which INSERTs to the same table...
    Or earlier on primary key violation.

    Probably you want to UPDATE the columns in CUSTOMER_INFO table from associated row in ROOM table, which is possible by simple assignment
    Code:
    BEGIN
      SELECT catagori, quality, price
        INTO :new.catagori, :new.quality, :new.price
      FROM room
      WHERE room_no=:new.room_no;
    EXCEPTION
      WHEN no_data_found THEN
        <action to take when no ROOM with given ROOM_NO exists>;
    END;
    As ROOM_NO is primary key in CUSTOMER_INFO I wonder if the updated values will ever differ from the ones in ROOM table. But, this is the question on the one who designed these tables.

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    Hellow Tonmoy
    I have also faced with this error.You can try my way to solve yours.So what i did is first made a package and declared some variables to use them globaly on any trigger.
    Code:
    CREATE OR REPLACE PACKAGE test_package AS
      new_room_no customer_info.room_no%type;
    END;
    /
    After that create a trigger wich has the 'for each row' thing.This will take the new value and will assign it to the global varibale as we declared in package
    Code:
     create or replace trigger siam_trigger before INSERT ON customer_info 
    for each row
    begin 
    	test_package. new_room_no:=:new.room_no;
    end;
    /
    Now declare a final trigger for original query
    Code:
     create or replace trigger insert_audit before INSERT ON customer_info
    begin
          insert into customer_info(catagori,quality,price) select catagori,quality,price from room where room_no=test_package. new_room_no;
    end;
    I hope it will help you...

  4. #4
    Join Date
    Aug 2010
    Posts
    1
    i m very right with sixsic6.

Posting Permissions

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