auto increment in oracle table when insert in the same table-CodePudding

i have a table and i want to increament a column by 1 when i insert a row in 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 who 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 they give me that 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

CodePudding user response：

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).

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

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),
 ...);