Home » SQL & PL/SQL » SQL & PL/SQL » How to auto increment (Oracle 11g)
icon5.gif  How to auto increment [message #571510] Mon, 26 November 2012 12:51 Go to next message
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?
Re: How to auto increment [message #571511 is a reply to message #571510] Mon, 26 November 2012 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
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?

Re: How to auto increment [message #571512 is a reply to message #571511] Mon, 26 November 2012 13:00 Go to previous messageGo to next message
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.
Re: How to auto increment [message #571513 is a reply to message #571512] Mon, 26 November 2012 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+trigger+increment+PK
Re: How to auto increment [message #571514 is a reply to message #571510] Mon, 26 November 2012 13:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1794
Registered: January 2010
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.
Re: How to auto increment [message #571516 is a reply to message #571512] Mon, 26 November 2012 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
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

icon7.gif  Re: How to auto increment [message #571519 is a reply to message #571510] Mon, 26 November 2012 16:11 Go to previous messageGo to next message
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;
Re: How to auto increment [message #571526 is a reply to message #571510] Mon, 26 November 2012 20:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
select max(memberid) from customers;
Re: How to auto increment [message #571531 is a reply to message #571527] Mon, 26 November 2012 21:22 Go to previous messageGo to next message
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.
Re: How to auto increment [message #571534 is a reply to message #571531] Mon, 26 November 2012 21:36 Go to previous messageGo to next message
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
Re: How to auto increment [message #571535 is a reply to message #571510] Mon, 26 November 2012 22:01 Go to previous messageGo to next message
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>
Re: How to auto increment [message #571538 is a reply to message #571535] Mon, 26 November 2012 22:33 Go to previous message
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
Previous Topic: add aging columns to derive total balance overdue
Next Topic: Please can anyone help me sql script equivalent in oracle
Goto Forum:
  


Current Time: Sat Apr 19 00:39:01 CDT 2014

Total time taken to generate the page: 0.11328 seconds