swapnil.kale_at_gmail.com wrote:
> 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,
As Jim Smith indicates ... the fault here is with the data model. What
you are describing is modeled incorrectly. Reconsider this as a parent
child relationship where the first record is the parent of all
subsequent, detail, records.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Jan 19 2007 - 09:02:14 CST
