Re: TRIGGERS
- From: david wendelken <davewendelken@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 May 2005 11:08:27 -0700 (PDT)
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;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
