no data Found in trigger

six_sic6 · #1 (**permalink**) 07-27-10, 17:35

Hellow
I have a table netservice which is created by the following codecreate table

Code:

netservice(
		issued_num varchar(20),
		service varchar(20),
		balance float,
		foreign key (issued_num) references customer(issued_num)
);

Now i have the following trigger which will select the service field from the netservice table with respect to :new.balance.

Code:

create or replace trigger test_trigger before update on netservice
for each row
declare
    servc netservice.service%type;
begin
	select service into servc from netservice where balance=:new.balance;
	if servc='P2' then
		raise_application_error(-20000,'Got the desired values');
	end if;
end;
/

Before updating i have inserted a value using the following query
[CODE]
insert into netservice values('01819421877','P2',1000);
[/CODE

But its giving me a table is mutating error...

can anyone tell me how to solve it??...

Littlefoot · #2 (**permalink**) 07-27-10, 17:46

There are zillion of documents that cover a mutating table issue. Google, read.

In order to solve it, simply check a value you have just entered. Code would look like this:

Code:

if :new.service = 'P2' then
   raise_application_error(-20000, 'Got the desired values');
end if;

No select, no nothing. A simple IF will do.

Internet Services

Web Development

Digital Marketing

Consumer Tech