Why this sequence increments by 2?

Question

I can't understand why this sequence is incremented by 2.

Is there any error in sequence to increment by 1? I need this to insert primary key value in table 'food'.

CREATE SEQUENCE food_id_ai START WITH 1 INCREMENT BY 1 CACHE 100;

create table food(
    food_id  integer,
    f_name   varchar(30) not null,
    category varchar(30) not null,
    price    number(4),
    amount   number(4)  
);

alter table food add constraint fpk primary key(food_id);

CREATE OR REPLACE TRIGGER insert_into_food
  BEFORE INSERT ON food
  FOR EACH ROW
BEGIN
  :new.food_id:= food_id_ai.nextval;
END;
/

insert into food values(food_id_ai.nextval,'ruchi', 'chanachur' , 8, 50);
insert into food values(food_id_ai.nextval,'chips', 'chips' , 8, 50);
insert into food values(food_id_ai.nextval,'aeromatic', 'soap' , 8, 50);
insert into food values(food_id_ai.nextval,'handwash', 'toyletries', 8, 50);
insert into food values(food_id_ai.nextval,'tissue', 'toyletries' , 8, 50);

Answer 1

Because you're accessing the sequence both in your INSERT statement and in the trigger that is launched for each row, of course it's incremented by two.

Choose one.
I'd go for the trigger-based one, since you won't have to remember to specify the sequence in each insert statement you may execute.

In that case, you'll have to explicitly list the columns you are going to insert VALUES to:

INSERT INTO food (f_name, category, price, amount)
VALUES ('ruchi', 'chanachur' , 8, 50);

Answer 2

you have two options to correct this.

1. modify insert statement to be like this:

   insert into food (f_name, category,price , amount)
   values ('ruchi', 'chanachur' , 8, 50);
   

2. or modify you triggers as follow:

   CREATE OR REPLACE TRIGGER insert_into_food
   BEFORE INSERT ON food
   FOR EACH ROW
   BEGIN
     if :new.food_id is null then 
       :new.food_id:= food_id_ai.nextval;
     end if;
   END;
   /