Re: Need workaround for Oracle Mutating table

In message <1169188977.032262.274390_at_51g2000cwl.googlegroups.com>, swapnil.kale_at_gmail.com writes
>
>Jim Smith wrote:
>> In message <1169129565.784139.136830_at_q2g2000cwa.googlegroups.com>,
>> swapnil.kale_at_gmail.com writes
>> >
>> >Hi there,
>> >
>> >I wish to insert two values as 0 if there exists a similar record in
>> >the database. Here goes the code for the same.
>> >
>> >The trigger is like this:
>> >
>> >CREATE OR REPLACE TRIGGER BEFORE_CHANGE_SVM
>> > BEFORE INSERT OR UPDATE
>> > ON ST_VOL_MNTH
>> > FOR EACH ROW
>> >REC := RY_CGE(:NEW.X,:NEW.Y,:NEW.Z);
>> >--the function is defined below.
>> > IF (REC>0) THEN
>> >-set the new value.abc :=0;
>> > END IF;
>> >END;
>> >
>> >
>> >Where the function is like this. which returns the number of records
>> >present in the table matching the criterea,
>> >
>> > SELECT COUNT(*) INTO REC FROM ST_VOL_MNTH SVM
>> > where values are matching.
>> >
>> >I'm getting the mutation error as i'm trying to query the same table on
>> >which the trigger is written.
>> >It would be great if somebody can help!
>> >
>> >Thanks in advance.
>> >Swapnil Kale.
>> >
>> Couldn't you achieve this using a unique constraint on the relevant
>> columns?
>>
>> begin
>> insert ... -- original values
>> exeception
>> when dup_val_on_index then
>> insert ... -- replacement values
>> end;
>> --
>> Jim Smith
>> Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
>> RSS <http://oracleandting.blogspot.com/atom.xml>
>
>
>
>Hi Jim,
>Thanks for the reply.
>
>The concern was not check for duplicate index.
>The requirement is there could be multiple records with the key
><compoiste primary key>. one of the primary key is a sequence number.
>As there could be multiple records with the same key on the same date,
>the sequence number changes.<next record>
>My requirement is if there exists a record on the same day, <except for
>the first record. so we are checking record_count>0> the subsequent
>records for the same day have some charges as 0. Those charges should
>not get repeated as the first record already has it,
>

Sounds like the data model isn't quite right. If the charges are not dependent on the primary key, they shouldn't be in that table.

You should have a daily charges table keyed on (<whatever>+date) and the table you described keyed on (<whatever>+date+sequence). Alternatively, you could key it on (<whatever>+datetime) and drop the sequence.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>