Re: trigger puzzlement

One other thought -

should I be writing an error handler for the 1403 condition? Or is there a way to phrase this query so one doesn't get an error?

Thanks

Mike

"Mike Stenzler" <mstenzler_at_ssaris.com> wrote in message news: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 - 14:06:30 CST