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