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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.