OraFAQ Forum: SQL & PL/SQL » How to auto increment

Home » SQL & PL/SQL » SQL & PL/SQL » How to auto increment (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to auto increment [message #571510]

Mon, 26 November 2012 12:51

raymak
Messages: 15
Registered: November 2012

Junior Member

Hi, I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.

So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....

I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.

So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?

Report message to a moderator

Re: How to auto increment [message #571511 is a reply to message #571510]

Mon, 26 November 2012 12:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
you must use SQL.

are both SEARCH of this forum & GOOGLE broken for you?
Do you think you are the first & only person with such a requirement?

Report message to a moderator

Re: How to auto increment [message #571512 is a reply to message #571511]

Mon, 26 November 2012 13:00

raymak
Messages: 15
Registered: November 2012

Junior Member

I did GOOGLE! Otherwise, I wouldn't ask for here! All the references I am getting are for mysql not for oracle.

Report message to a moderator

Re: How to auto increment [message #571513 is a reply to message #571512]

Mon, 26 November 2012 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.lmgtfy.com/?q=oracle+trigger+increment+PK

Report message to a moderator

Re: How to auto increment [message #571514 is a reply to message #571510]

Mon, 26 November 2012 13:10

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

raymak wrote on Mon, 26 November 2012 13:51

So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?

Based on SG0101 max sequence can go up to is SG9999. If so:

1. Create sequence with minvalue 101 and maxvalue 9999.
2. Create trigger which assigns PK column value 'SG' || TO_CHAR(your_sequence.NEXTVAL,'FM9999')

SY.

Report message to a moderator

Re: How to auto increment [message #571516 is a reply to message #571512]

Mon, 26 November 2012 13:15

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition "SG" does not add anything to the data, so it should be in it.
Why wasting 2 or 4 bytes in each row (and add complexity in code) with something useless?

Regards
Michel

[Updated on: Mon, 26 November 2012 13:15]

Report message to a moderator

Re: How to auto increment [message #571519 is a reply to message #571510]

Mon, 26 November 2012 16:11

raymak
Messages: 15
Registered: November 2012

Junior Member

Hi Solomon & Michel,

First of all thank you for your time and great response and understanding what I am trying to say. "Unlike someone in this post has reading problem here."

Michel, I agree with you, but my company wants "SG" in their memberid every time new customer is created. Its been like this for years and they are not willing to change that.

Solomon, I created a sequence with memberid_seq, but how to I add values in trigger? "'SG' || TO_CHAR(your_sequence.NEXTVAL,'FM9999')"

create or replace 
trigger "bi_MEMBERID" 
  before insert on "CUSTOMERS"              
  for each row 
begin  
  if :new."MEMBERID" is null then
    select "memberid_SEQ".nextval into :new."MEMBERID" from sys.dual;
  end if;
end;

Report message to a moderator

Re: How to auto increment [message #571526 is a reply to message #571510]

Mon, 26 November 2012 20:41

raymak
Messages: 15
Registered: November 2012

Junior Member

So here is what I did so far:

I created sequence called MEMBERID_SEQ and trigger called bi_MEMBERID

Here are the codes:

CREATE SEQUENCE  "RAHIL"."MEMBERID_SEQ"  MINVALUE 1 
MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 211 CACHE 20 NOORDER  NOCYCLE ;

Trigger:

create or replace 
trigger "bi_MEMBERID" 
  before insert on "CUSTOMERS"              
  for each row 
begin  
  if :new."MEMBERID" is null then
    select 'SG' || To_char(MEMBERID_SEQ.nextval,'FM9999') into :new."MEMBERID" from sys.dual;
  end if;
end;

So when I add the following command below, I get an error called:

SQL> Insert into  CUSTOMERS (CUSTID,MEMBERID,FNAME,MNAME,LNAME,CONTACTNAME,BNAME
,FEDERALID,BADDRESS,BCITY,BSTATE,BZIPCODE,MAILADDRESS,
MCITY,MSTATE,MZIPCODE,TELE
PHONE,CELLPHONE,FAX,EMAIL,JOINDATE,STATUS,ZONE) 
values (CUSTOMERS_SEQ.nextval,'M
EMBERID_SEQ.nextval','James',null,null,null,null,null,null,
null,null,null,null,n
ull,null,null,null,null,null,null,null,null,null);
Insert into  CUSTOMERS (CUSTID,MEMBERID,FNAME,MNAME,LNAME,CONTACTNAME,BNAME,FEDE
RALID,BADDRESS,BCITY,BSTATE,BZIPCODE,MAILADDRESS,MCITY,
MSTATE,MZIPCODE,TELEPHONE
,CELLPHONE,FAX,EMAIL,JOINDATE,STATUS,ZONE) 
values (CUSTOMERS_SEQ.nextval,'MEMBER
ID_SEQ.nextval','James',null,null,null,null,null,null,
null,null,null,null,null,n
ull,null,null,null,null,null,null,null,null)
*
ERROR at line 1:
ORA-00001: unique constraint (RAHIL.CUSTOMERS_PK) violated

Insert into  CUSTOMERS (CUSTID,MEMBERID,FNAME,MNAME,LNAME,CONTACTNAME,BNAME,FEDERALID,BADDRESS,BCITY,
BSTATE,BZIPCODE,MAILADDRESS,MCITY,MSTATE,MZIPCODE,TELEPHONE,CELLPHONE,FAX,EMAIL,JOINDATE,STATUS,ZONE)
 
values (CUSTOMERS_SEQ.nextval,'MEMBERID_SEQ.nextval','James',null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null);

[Updated on: Tue, 27 November 2012 01:07] by Moderator

Report message to a moderator

Re: How to auto increment [message #571527 is a reply to message #571526]

Mon, 26 November 2012 20:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select max(memberid) from customers;

Report message to a moderator

Re: How to auto increment [message #571531 is a reply to message #571527]

Mon, 26 November 2012 21:22

raymak
Messages: 15
Registered: November 2012

Junior Member

SQL> select max(memberid) from customers;

MAX(MEMBERID)
------------------------------
SG0190

I did change the starting value in the sequence and its same.

Report message to a moderator

Re: How to auto increment [message #571534 is a reply to message #571531]

Mon, 26 November 2012 21:36

raymak
Messages: 15
Registered: November 2012

Junior Member

I changed the start with value in CUSTOMERS_SEQ from 191. But now here is what I get when I insert data:

One error saving changes to table "RAHIL"."CUSTOMERS":
Row 42: ORA-01481: invalid number format model
ORA-06512: at "RAHIL.bi_MEMBERID", line 3
ORA-04088: error during execution of trigger 'RAHIL.bi_MEMBERID'
ORA-06512: at line 1

Report message to a moderator

Re: How to auto increment [message #571535 is a reply to message #571510]

Mon, 26 November 2012 22:01

tony123
Messages: 9
Registered: August 2012

Junior Member

this is a sample.

SQL> CREATE TABLE customers(memberid VARCHAR2(6) PRIMARY KEY,other INT);

Table created
SQL> CREATE SEQUENCE SEQ_MEMBERID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 211 CACHE 20 NOORDER NOCYCLE ;

Sequence created
SQL> CREATE OR REPLACE TRIGGER bi_MEMBERID
2 BEFORE INSERT ON CUSTOMERS
3 FOR EACH ROW
4 BEGIN
5 SELECT 'SG' || TO_CHAR(SEQ_MEMBERID.NEXTVAL, 'FM0000') INTO :new.MEMBERID FROM SYS.DUAL;
6 END;
7 /

Trigger created
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 INSERT INTO customers(other) VALUES(i);
4 END LOOP;
5 COMMIT;
6 END;
7 /

PL/SQL procedure successfully completed
SQL> SELECT * FROM customers;

MEMBERID OTHER
-------- ---------------------------------------
SG0211 1
SG0212 2
SG0213 3
SG0214 4
SG0215 5
SG0216 6
SG0217 7
SG0218 8
SG0219 9
SG0220 10

10 rows selected

SQL>

Report message to a moderator

Re: How to auto increment [message #571538 is a reply to message #571535]

Mon, 26 November 2012 22:33

raymak
Messages: 15
Registered: November 2012

Junior Member

Tony,

that was a great example. Smile

it worked!

So I had to change the value in SELECT 'SG' || TO_CHAR(SEQ_MEMBERID.NEXTVAL, FM9999) INTO :new.MEMBERID FROM SYS.DUAL; to FM0000 and it worked! Smile

Thank you so much. I will keep this example for future use. Smile

Report message to a moderator

Previous Topic:	add aging columns to derive total balance overdue
Next Topic:	Please can anyone help me sql script equivalent in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 04 18:26:58 CDT 2025