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