Trigger problem

Tanmoy Saha · #1 (**permalink**) 07-21-10, 01:51

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

flyboy · #2 (**permalink**) 07-21-10, 03:50

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.

six_sic6 · #3 (**permalink**) 07-27-10, 17:53

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

skudo · #4 (**permalink**) 08-03-10, 17:14

i m very right with sixsic6.

Internet Services

Web Development

Digital Marketing

Consumer Tech