Re: trigger puzzlement

From: Mike Stenzler <mstenzler_at_ssaris.com>
Date: Thu, 12 Feb 2004 15:04:14 -0500
Message-ID: <M_QWb.408$ys5.222_at_fe08.usenetserver.com>


Sorry- left out the complete error info:

when I attempt to execute from SQL+ I get the following 3 errors:

ORA-01403: no data found
ORA-06512: at "TEST.AUROW_GRID_CLONE", line 8
ORA-04088: error during execution of trigger 'TEST'.AUROW_GRID_CLONE'

this highlights that the select for pre-existance of "today's" t2.record is causing a problem when there is no record. I was under the impression that using the EXISTS statement was a way to query a table w/o getting a SQL_NOTFOUND returned as an ERROR value.

Do I have to somehow turn off error processing before this query. In Pro*C I can do this with the
EXEC SQL WHENEVER ERROR CONTINUE: ideas?

"Mike Stenzler" <mstenzler_at_ssaris.com> wrote in message news:BAOWb.78592$va1.36814_at_fe23.usenetserver.com...
> This may be obvious, but I don't write many triggers so it's got me
> puzzled..
>
> Row level, after update trigger is designed to perform an insert or update
> to a 2nd table based on existance of a record in the 2nd table.
>
> t1 is updated and has trigger, whenever an update happens, it inserts or
> updates a record in t2. If there has already been an insert "today" (using
> SYSDATE w/ timestamp stripped out) we perform an update on t2, otherwise
we
> insert.
>
> Problem is code works fine if we comment out the check for existance and
> just insert records. code works OK if there is an existing record and we
> perform an update. However - if we check for existance and there is no
> record, instead of inserting, we come up with an error saying we can't
> perform an update - another process has a lock.
>
> DATA
>
> CREATE TABLE TEST.T1
> (
> CMDY_SYM VARCHAR2(6)
> ,ZONE1 NUMBER(6,2)
> );
>
> CREATE TABLE TEST.T2
> (
> LAST_CHANGE DATE,
> CMDY VARCHAR2(6)
> ,GV NUMBER(6,2)
> );
>
> code:
>
> "TEST"."AUROW_GRID_CLONE" AFTER
> UPDATE OF "ZONE1" ON "TEST"."T1" FOR EACH ROW
>
> declare v_exists VARCHAR2(6) :='FALSE';
>
> BEGIN
>
> -- test if record has already been inserted today
> select 'TRUE' into v_exists from DUAL where EXISTS
> (select * from test.t2
> where last_change = TO_DATE(TO_CHAR(SYSDATE(),
'MM-DD-YYYY'),'MM-DD-YYYY')
> and cmdy = :new.cmdy_sym
> );
>
> if (v_exists = 'TRUE') then
> update test.t2
> set gv = :new.zone1
> where last_change = TO_DATE(TO_CHAR(SYSDATE(),
> 'MM-DD-YYYY'),'MM-DD-YYYY')
> and cmdy = :new.cmdy_sym;
> else
> insert into test.t2
> ( last_change, cmdy, gv )
> values
> (
> TO_DATE(TO_CHAR(SYSDATE(),'MM-DD-YYYY'),'MM-DD-YYYY'),
> :new.cmdy_sym,
> :new.zone1
> );
> end if;
>
> END;
>
> Any ideas as to whart I'm doing wrong?
>
> Oracle 9.2.0.3
>
> Mike
>
>
>
Received on Thu Feb 12 2004 - 21:04:14 CET

Original text of this message