Hi,
When i insert data into any table last number of sequnce get increased in all sequences that are listed below. These are for auto incremented numbers. All tables have their own autoincremented coloumns. Can anyone see mistake? Thanks

CREATE SEQUENCE FRANC_AUTOINC_SEQ
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE;

CREATE TRIGGER FRANC_AUTOINC_TRG
  BEFORE INSERT ON FRANCHISE
  FOR EACH ROW
    BEGIN
      SELECT FRANC_AUTOINC_SEQ.NEXTVAL INTO :NEW.FRANCHISEID FROM DUAL;
    END;

CREATE SEQUENCE EMP_AUTOINC_SEQ
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE;

CREATE TRIGGER EMP_AUTOINC_TRG
  BEFORE INSERT ON EMPLOYEE
  FOR EACH ROW
    BEGIN
      SELECT EMP_AUTOINC_SEQ.NEXTVAL INTO :NEW.EMPLOYEEID FROM DUAL;
    END;

CREATE SEQUENCE MNG_AUTOINC_SEQ
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE;

CREATE TRIGGER MNG_AUTOINC_TRG
  BEFORE INSERT ON MANAGER
  FOR EACH ROW
    BEGIN
      SELECT MNG_AUTOINC_SEQ.NEXTVAL INTO :NEW.MANAGERID FROM DUAL;
    END;

CREATE SEQUENCE CUST_AUTOINC_SEQ
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE;

CREATE TRIGGER CUST_AUTOINC_TRG
  BEFORE INSERT ON CUSTOMER
  FOR EACH ROW
    BEGIN
      SELECT CUST_AUTOINC_SEQ.NEXTVAL INTO :NEW.CUSTOMERID FROM DUAL;
    END;

Recommended Answers

All 2 Replies

i have just deleted all sequences and inserted one data into a table. Then sequence's last number become 21 instead of 1.
???

Have run the nextval from any other query or only for testing the values ? it never happens that it will get incremented automaticalyy or affect other sequence objects.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.