Mutating Table Problem. Please help...

From: Dorothy Foster <dor_foster_at_hotmail.com>
Date: 18 May 2002 10:56:03 -0700
Message-ID: <a8ecbe66.0205180956.3caa4d89@posting.google.com>

Oracle version is 8.1.7, Sun Solaris. Using TOAD to access Oracle. Hello....
I am having a strange mutating problem and I have tried several ways to get over it. But it seemed impossible , I am unable to achieve the result that I am looking for. Will someone please correct me where I went wrong. I literally tried to work it out with all possible options I got from the documentation but it was not ok. I just wanted to know whether someone have had a similar experience before and thought would request the solution for this ...

basically i have a table,
sql > create table credit_test (customer_id number, site_id number, hold varchar2(4))

sql > insert into credit_test values(293, 381,'N'); sql > insert into credit_test values(293, 547, 'N');

sql> insert into credit_test values(293, 557, 'N');
sql> insert into credit_test values(293, 988,'N');
sql> insert into credit_test values(293, null, 'N');

sql> select * from credit_test;
CUSTOMER_ID	   SITE_ID  	HOLD
        293	     381	  N
	293	     547	  N
	293	     557	  N
	293	     988	  N
	293		          N
________________________________________________________

The requirement is that when the end user updates the hold to a value(Y/N) WHERE THE SITE_ID IS NULL, then the new hold value(Y/N) should be updated on all the other HOLDs where the site_id is not null and where the customer_id is the same. That is , If I update the hold for the customer_id = 293 and site_id is null to Y, then the trigger should also update the HOLD = 'Y' where the customer_id = 293 and the site_use_id is not null.

I tried writing a after update trigger for each row that would capture the :new.customer_id, :new.site_id, :new.HOLD into a pack of variables and then wrote an after update trigger that would capture these values and then updates the other rows. But it did not seem to work any bit. I cross-checked many documentation but did not really arrive at a solution even after working all night. I debugged each and every statement and everything went well,, but not updating the other values.

Please share some info on it..I am requesting so because that I wanted to say that I tried and tried and tried and seeking your help as a last resort which I think is the best thing I am doing.

Thanks
Dorothy... Received on Sat May 18 2002 - 12:56:03 CDT