XPost: comp.database.oracle, comp.databases.oracle.server   
   From: mstenzler@ssaris.com   
      
   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"  wrote in message   
   news:M_QWb.408$ys5.222@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"  wrote in message   
   > news:BAOWb.78592$va1.36814@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   
   > >   
   > >   
   > >   
   >   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)