XPost: comp.database.oracle, comp.databases.oracle.server   
   From: mstenzler@ssaris.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)