Search code examples
sqloracle-databaseauto-increment

Oracle - How to make auto-increment column with varchar type?

In my assignment with Oracle 11g, I am asked to make a table with column has this structure:

    [NL|TE|][0-9]^10

Where NL or TE is inputed when INSERT row and [0-9]^10 is an auto-increment 10 digits number. Example:

    NL1234567890 or TE0253627576

When INSERT, the user should only write this:

    INSERT INTO TableA VALUES ('NL');

And the DBMS take care of the rest. So how can I do so? Im still a newbie in this thing.

Solution 
  • CREATE SEQUENCE your_seq;
/

CREATE OR REPLACE TRIGGER your_tablename_BI
BEFORE INSERT
ON your_tablename
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
 :NEW.your_col := :NEW.your_col || trim(to_char(your_seq.nextval, '0000000000'));

END your_tablename_BI;
/