0

I am using Oracle SQL. There is a sequence and trigger used for id that increments from 1 every time a new record is added in the table. However if all the records are deleted and new records to be added this table, the id doesn't starts from 1 whilst it starts from the last maximum number of records recorded in the sequence.

Therefore is there a way in TRIGGER statement where I can reset the sequence if the table is empty and new records get imported.

OR do I have to do stored_procedure way, if that then how can i call this using myBatis mapper?

Table (customer_info)

customer_id customer_name customer_location
1           Arbin         USA
2           Tim           Canada
3           Rachel        Australia

Sequence

CREATE sequence customer_id_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Trigger

CREATE OR REPLACE TRIGGER customer_id 
BEFORE INSERT ON customer_info 
FOR EACH ROW

BEGIN
 SELECT customer_id_seq.NEXTVAL
 INTO   :new.customer_id
 FROM   dual;
END;
/

Thank you.

John Smith
  • 129
  • 2
  • 15

1 Answers1

2

Once You Truncate the Table. The Sequence has to be Reset to 1.

The simplest way to do it it by dropping and re-creating it again.

 DROP SEQUENCE customer_id_seq
 CREATE sequence customer_id_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
pmi
  • 341
  • 3
  • 6
  • 1
    Simplest? Probably. Best option? No. A better option would be to alter the sequence, hange `increment by` value to - <> , generate next value, and change `increment by` value back to 1. If there is granted `select` privilege on that sequence to another user they should be re-granted again once you drop and recreate the sequence. – Nick Krasnov Jun 12 '17 at 21:07
  • @Mikolas Pansky, If I implement that way then it won't be robust, it would consume time everytime to DROP and CREATE. Therefore I was looking to make it automatic. – John Smith Jun 12 '17 at 21:20
  • Of course the INCREMENT using negative value is very much the solution that's more elegant. And even without using the DDL. – pmi Jun 12 '17 at 21:21
  • @NicholasKrasnov, you are right. I have to go through all those process you have mentioned. – John Smith Jun 12 '17 at 21:22