Re: Mutating Table Problem. Please help...

"Dorothy Foster" <dor_foster_at_hotmail.com> wrote in message news:a8ecbe66.0205180956.3caa4d89_at_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...

The general strategy is
before insert or update *statement* trigger setting up the pl/sql array
after insert or update *for each row* trigger capturing the primary keys affected
after insert or update *statement* trigger processing the array.

Without your actual code, I don't think you will get more specific advice.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address