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