Re: TRIGGERS

That's very interesting.

I tried this, and it let me query the myclass table from a myclass row-level 
trigger.  
I even tried some variations on the select statement and it didn't give a 
mutating table error as expected.

Hmmm.  Not at all the behaviour I was expecting.  

Did some more tests, and the mutating table error showed up when I did a 
multi-record insert in a single statement.  Don't remember Oracle being that 
smart back when I first learned about mutating table errors!
Learn something every day.

As I've written several times before, you are following a flawed approach - 
because you can still get the mutating table error if you continue down the 
path you are on.  

Do what I suggested, which is either use the code we gave you, or learn how to 
use autonomous transactions to avoid the mutating table error.


As for what else wrong with the code below, your looping approach is 
implemented in a bass-ackwards way - you've got several really bad logic errors 
in what you've done.

Is this a homework assignment?


-----Original Message-----

the error i get :

ORA-0001: unique constraint violated

Here is the entire create trigger

CREATE OR REPLACE TRIGGER "TESTDB"."MYCLASS_TR" BEFORE
INSERT
OR UPDATE ON "MYCLASS" FOR EACH ROW DECLARE
  REFCOUNT            NUMBER;
  DUP_VAL_ON_INDEX    EXCEPTION;
  TEMP                NUMBER;
 
BEGIN
   IF INSERTING THEN
      
      
  LOOP      
      SELECT COUNT(*) INTO REFCOUNT
      FROM MYCLASS
      WHERE :NEW.ID = ID;
                  
      IF REFCOUNT > 0 THEN --RECORD EXISTED IN
DATABASE
      SELECT MYCLASS_SEQ.NEXTVAL INTO TEMP FROM DUAL;
      END IF;     
      EXIT;
     --ELSIF REFCOUNT < 0 THEN
     -- EXIT;
     -- END IF;
      
              
      
  END LOOP;    
      
      IF :NEW.ID IS NULL THEN
      
            
      SELECT MYCLASS_SEQ.NEXTVAL 
      INTO :NEW.ID 
      FROM DUAL;
      END IF;
         
   END IF; --MAIN IF


END;

--
http://www.freelists.org/webpage/oracle-l

Other related posts: