Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Red face Unanswered: no data Found in trigger

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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

Posting Permissions

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