XPost: comp.databases.oracle.server   
      
   Hi Mike,   
      
   Please post the exact ORA- error message next time.   
      
   I tried to recreate, and I got the error below, which I belive you got   
   too. But it has nothing to do with locks.   
      
   ORA-01403: no data found   
   ORA-06512: ved "A.AUROW_GRID_CLONE", linje 6   
   ORA-04088: fejl under udf°relse af triggeren 'A.AUROW_GRID_CLONE'   
      
   Which means that your intitial check returns no row => no data found   
   to put into v_exists => exception is raised. This is the expected   
   behaviour.   
      
   What you need to do is to catch that exception and handle it   
   appropriately. You could also make your check more readable (and even   
   prettier), like this :   
      
   begin   
   select 1   
     into dummy_var   
    from test.t2   
   where trunc(last_date) = trunc(sysdate); --today   
   and cmdy = :new.cmdy_sym;   
      
   -- No exception, There was exactly one row, do the update here   
   exception   
   when no_data_found then   
     -- there was no record for today, do the insert here.   
   when too_many_rows then   
     -- Something went terribly wrong !   
    raise;   
   end;   
      
      
   - Kenneth Koenraadt   
      
      
      
      
      
      
      
   On Thu, 12 Feb 2004 14:52:18 -0500, "Mike Stenzler"   
    wrote:   
      
   >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)