Q

How to automatically increment customerID

I would like a customerID (number e.g.10001) of the customer table to be incremented automatically when a new customer is inserted into the table. How do I proceed? Note CustomerID is the primary key.
In Oracle, you'll have to create a sequence to incrementally generate your number. A command similar to the following can help:

CREATE SEQUENCE cust_id_seq START WITH 10001 INCREMENT BY 1;

To have Oracle automatically populate the CUST_ID column of the CUSTOMERS table with the next value in this sequence, you'll need a trigger similar to the following:

CREATE TRIGGER customer_trig
BEFORE INSERT ON customer
FOR EACH ROW
DECLARE
  customer_id NUMBER;
BEGIN
  SELECT cust_id_seq.NEXTVAL INTO customer_id FROM dual;
  :new.cust_id := customer_id;
END;
/

The sequence will automatically generate the next number in the sequence whenever you select the next value of the sequence. The trigger will fire every time a row is inserted into the table. It will set the new CUST_ID column to the value found in the CUSTOMER_ID variable. This variable is set to the NEXTVAL in the sequence.

This was first published in April 2004
This Content Component encountered an error

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close