CODEWITHSUNDEEP
oracle-databaseoracle10g
user3445854
user3445854

Reputation: 21

Generate automatic Id

I am making a web application which uses a database in which I have a field I_ID which i want to automatically increment like I0 then I1 then I2 and so on with each record insertion in the database.

To achieve it I made a trigger for this table.But its not working fine.What can be the reason.Please help

My Trigger T1: 

CREATE OR REPLACE TRIGGER  "T1" 
before
insert on "TBINDIVIDUAL"
for each row
declare 
x varchar2(10);
mx varchar2(13);
mx2 varchar2(13);
y number(3);
begin
x:=:new.I_ID;
mx:=substr(x,1,1);
select max(I_ID) into mx2 from tbindividual where I_ID like mx||'%';
y:=to_number(substr(mx2,2));
:new.I_ID:=mx||to_char(y+1);
end t1;
/

EDITED : As i do by answer 

CREATE OR REPLACE TRIGGER  "TBINDIVIDUAL_T1" 
BEFORE
insert on "TBINDIVIDUAL"
for each row
begin
:new.I_ID = SEQ1.nextval;
end;
/

But it give two errors

Encountered the symbol "=" when expecting one of the following: := . ( @ % ; indicator

Encountered the symbol "END"

Please help

Upvotes: 0

Views: 50

Answers (1)

evenro
evenro

Reputation: 2646

So Oracle is not SQL Server... if you want to get unique ID's, you need to populate them from a sequence.

for creating a sequence use:

create sequence myseq;

and in your code use (depends on the version):

:new.I_ID := myseq.nextval;

or 

select myseq.nextval into :new.I_ID from dual;

the problem your code doesn't work is what :new and :old means... I would recommend you reading about their meaning...

Hope I've been helpful...

Because of the discussion in the comments - here is a full example: for this table:

CREATE TABLE test (A number);

to add a unique, sequential ID you need to first create a sequence:

CREATE SEQUENCE myseq;

and a trigger:

CREATE OR REPLACE TRIGGER "T1" before insert on "test" for each row begin :new.I_ID := myseq.nextval; end t1; /

by the way - I would recommend to check before substituting :new.I_ID, if it is null or not, cause sometimes in upgrades people add a unique ID from an external resource.. (such as them getting a unique number from the sequence themselves...)

you can read more about sequences here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314

sorry for the way the code is displayed.. need to learn how to write code here...

One more thing - In Oracle - you cannot create such a PL/SQL to increase existing counter without locks. Concurrent queries might run the first query in the PL/SQL simultaneously, which means multiple sessions will get the same I_ID.

Also notice that in your code you queried the max on varchar, which is not the same as max on number...

Adding concatenated text is unrelated to the unique ID. In your case it will look like:

:new.I_ID = substr(:new.I_ID,1,1)||to_char(myseq.nextval);

assuming x is being inputted with the char you want..

Upvotes: 1

Related Questions