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