CODEWITHSUNDEEP
sqloracle-databasesequence
Abdeladim Benjabour
Abdeladim Benjabour

Reputation: 23

Auto-increment in Oracle table when insert in the same table

I have a table and I want to increment a column by 1 when I insert a row into the same table.

Table users - when I insert first row value of idusers is 1, and in second row value is 2 ....

This is the table

USERS

    EMAIL primary key
    USERNAME
    PASSWORD
    IDUSER and this the column I want to be AUTO_INCREMENT

I have tried this code

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;


create or replace trigger incrementIdUser
    before insert on users
    for each row
    begin
        select seq_person.nextval into :new.IDUSER from users;
    end;

But I get an error when I insert a row:

Erreur lors de l'enregistrement des modifications de la table "SOCIAL"."USERS" :
Ligne 1 : ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SOCIAL.INCREMENTIDUSER", line 2
ORA-04088: error during execution of trigger 'SOCIAL.INCREMENTIDUSER'
ORA-06512: at line 1

Upvotes: 2

Views: 149

Answers (4)

Viktor Török
Viktor Török

Reputation: 1319

Instead of using a trigger, you should use an identity column in the create table statement:

create table users
(iduser integer generated by default on null as identity (nomaxvalue nocache order),
 ...);

Upvotes: 0

MT0
MT0

Reputation: 167972

You get that error because there are zero rows in the USERS table so SELECT ... FROM USERS returns no rows.

What you want is to either use a table that will always return a single row (which, in Oracle, is the DUAL table):

create or replace trigger incrementIdUser
    before insert on users
    for each row
    begin
        select seq_person.nextval into :new.IDUSER from DUAL;
    end;

Or, the better solution, is to not use an SQL statement and use pure PL/SQL:

create or replace trigger incrementIdUser
    before insert on users
    for each row
    begin
        :new.IDUSER := seq_person.nextval;
    end;

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Insetead of select seq_person.nextval into :new.IDUSER from users; to assign sequence value into iduser you need to use :new.IDUSER :=seq_person.nextval;

create or replace trigger incrementIdUser
    before insert on users
    for each row
    begin
         :new.IDUSER :=seq_person.nextval;
    end;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

Not like that, but

create or replace trigger incrementIdUser
  before insert on users
  for each row
begin
    :new.iduser := seq_person.nextval;
end;

Code you wrote selects from users table (which is empty, hence NO_DATA_FOUND). If it contained more than a single row, you'd get TOO_MANY_ROWS (as you're selecting into a scalar variable (:new.iduser). Finally, there's danger of mutating table error as you can't select from a table which is just being modified (in this trigger type).

Upvotes: 2

Related Questions