Q

Undo mutating a sequence

In my program I use a sequence. When I push a button to add a customer, then I use a nextval on my sequence. The problem is, when I decide to stop adding a customer, my sequence is still going up. Is there a way to undo mutating a sequence?
Hmmm. When you use a sequence, you will always increase the latest value when you use nextval... However, it sounds like your button event is not completing properly. Another possibility from your question is that the sequence increments one more time, so that if you exit your application, then come back later to add a new customer, the sequence numbers used have a hole... (X, X+1, X+3)

Since I'm not entirely sure which case we're dealing with, here's an example of how you may wish to populate your

customer number --

CREATE OR REPLACE TRIGGER generate_customer_id

BEFORE INSERT OR UPDATE ON customers

FOR EACH ROW

BEGIN

/* fill in the id field of the customer table with the next value from the customer_sequence. Since ID is a column in customers, :new.ID is a valid reference */

     SELECT customer_sequence.NEXTVAL

     INTO :new.ID

     FROM dual;


END    generate_customer_id;


----

Then, to build the information into the customer table, use the information like this:

INSERT INTO customers (ID, first_name, last_name) values (-7, 'Larry', 'Ellison').

Although we have put a value in the ID field, the trigger replaces it with the value in new.ID. The value in the insert statement allows it to pass syntax checking.

This was first published in January 2004

Dig deeper on Oracle E-Business Suite

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close