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>
Received on Thu Jan 18 2007 - 09:55:03 CST
