Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: TRIGGERS


From: david wendelken <>
Date: Wed, 18 May 2005 18:56:55 -0700 (PDT)
Message-ID: <>

>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 <> Sent: May 18, 2005 6:04 PM
Subject: Re: TRIGGERS

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


Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
Received on Wed May 18 2005 - 22:04:22 CDT

Original text of this message