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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: