Re: TRIGGERS

From: david wendelken <davewendelken_at_earthlink.net>
Date: Wed, 18 May 2005 18:56:55 -0700 (PDT)
Message-ID: <1533335.1116467815244.JavaMail.root@rowlf.psp.pas.earthlink.net>

>If you don't mind to correct , where i have gone wrong

I already gave you code that will work as long as you get the next value from the sequence, or make sure ahead of time that the value is not already used.

The only time you need to worry about it is if the id field fills up (numbers are up to 38 digits, not too likely a problem for most systems), or if you create the data in a test database and import it into production database (or vice-versa). Then you have to increment the sequences to equal the highest value in the id field. That's a pretty simple pl/sql script to write, and not something you need to do everyday. Given a reminder in a week or so, I would even such a script and share it with you. Or, someone may already have one handy and beat me to it!

If you take the trouble to supply the insert statement with a specified id value and that value is already taken, the database SHOULD raise an error. It can't do what you've EXPLICTLY told it to do. If you don't care what the id value is, then you should let the trigger do the work for you.

It's bad design to do otherwise. Several people have already told you this. Telling the database to explicitly use one value for the id and having it change that value without your knowledge is an absolutely horrible idea. To put it bluntly, it is highly likely to make application maintenance a living hell, not to mention corrupt one heck of a lot of data if you use this technique for all your tables.

I can't fix what you've written simply because the approach you are taking won't work reliably. Period. Ever. If you go down the path you are taking, you won't be able to use this form of the insert statement which allows you to insert more than one row at a time:

INSERT INTO the_table (col1, col2, col3) SELECT aa1, bb2, cc3 FROM some_other_table;

That is a mighty handy form of the insert statement and I wouldn't give it up lightly.

To understand why the approach you are taking will never work reliably, you need to do some research on mutating table errors. I've already told you that. It's not hard to find out about it, you just do a google search on "mutating table errors" and glance at several entries until you find a winner.

The way around the mutating table error in this case is through the use of autonomous transactions. You will need to read up on them, too.

Since you are new to pl/sql and Oracle, you need to prioritize what you learn.

Rather than learning about mutating table errors and autonomous transactions to implement a bad design, you need to focus on understanding the database transaction design using commit and rollback, and how the exception handlers work.

-----Original Message-----
From: Kean Jacinta <jacintakean_at_yahoo.com> Sent: May 18, 2005 6:04 PM
To: davewendelken_at_earthlink.net
Subject: Re: TRIGGERS

If you don't mind to correct , where i have gone wrong ?

JK

• david wendelken <davewendelken_at_earthlink.net> wrote:
  > 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
  >
  

  ---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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

Received on Wed May 18 2005 - 22:04:22 CDT