2

I have an oracle database table:

CREATE TABLE "DogInfo" (
"Id" NUMBER NOT NULL ENABLE,
"DogName" VARCHAR2 (50 CHAR) NOT NULL ENABLE,
"DogAge" NUMBER NOT NULL ENABLE,
CONSTRAINT "DogInfo_PK" PRIMARY KEY ("Id") ENABLE
);

CREATE SEQUENCE "DOGINFO_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

CREATE OR REPLACE TRIGGER  "BI_DogInfo" 
BEFORE INSERT ON "DogInfo" FOR EACH ROW
WHEN (NEW."Id" IS NULL OR NEW."Id" = 0) BEGIN
    SELECT "USERINFO_SEQ".nextval INTO :NEW."Id" FROM dual;
END;

ALTER TRIGGER  "BI_DogInfo" ENABLE;

If I insert 20 records into the table with database tool, and then use my C# web application to insert records, the dog id will start with 1, rather than 21.

Anyone can help me fix this bug?

Thanks.

Rock
  • 205
  • 1
  • 4
  • 14
  • in your C# code then are you passing the "Id" column? i guess you must be. the trigger is coded to only fire (when clause) if the ID isn't passed in. Also i'd advise you not to use quoted names in DB objects. – DazzaL Dec 10 '12 at 10:28
  • I'm not sure that I follow the question. Are you inserting the first 20 rows before you create the trigger? Are you inserting the first 20 rows by explicitly specifying an "Id" value, thus preventing the trigger from firing? Is your C# application passing in values for "Id" that cause the trigger to be bypassed (i.e. passing in a value of 1 explicitly)? – Justin Cave Dec 10 '12 at 10:29
  • the first 20 rows with id value. There is no "Id" column in my c# code. – Rock Dec 10 '12 at 10:49
  • In some cases, I need exact Id value, e.g: import old data. – Rock Dec 10 '12 at 10:50
  • what is the "database tool" generating then? paste you db side insert statment + c# code, as a sequence without cycle cannot jump back and reuse an ID that it has already generated. – DazzaL Dec 10 '12 at 11:01
  • Are you creating all the fixed-ID-value data first (i.e. importing all the old data) and all subsequent data will be inserted from your `c#` code? Or to put it another way, is there a clear, clean cut-off from the old fixed values and the new auto-increment values, or at the very least a ceiling for the old values? The 'normal' way to do this would be to create the sequence after all the old data is inserted, with `start with` set based on highest value in the imported data. The sequence can't automatically know about values you've set manually. – Alex Poole Dec 10 '12 at 18:52

3 Answers3

7

Sequence is not an "auto increment id".

Sequence is just a sequential unique number generator. It can work as your Id column value provider but it is up tu You to keep proper values in the column.

I assume that You add your 20 rows like this:

insert into table(id, <columns>) values (1, <values>);
insert into table(id, <columns>) values (2, <values>);
and so on ...

Your sequence has no way of knowing what is the next number you "expect" it to have (unless of course you (re)create it with desired initial value). Instead You should always use values from your sequence like this:

insert into table(id, <columns>) values (sequence.nextval, <values>);
insert into table(id, <columns>) values (sequence.nextval, <values>);
and so on ...

This way you'll keep sequence in sync with table id values.

EDIT :

You can imitate that behaviour by using trigger and sequence as described in this answer.

Also IDENTITY column is now available on Oracle 12c

Community
  • 1
  • 1
Grzegorz W
  • 3,487
  • 1
  • 21
  • 21
  • So it's a bug of oracle? :-) – Rock Dec 10 '12 at 11:02
  • It sounds like you're saying you cannot use a sequence along with a trigger to make an insert act like an auto-incrementing column. That does not seem like the case, e.g. [this answer](http://stackoverflow.com/a/11296469/498594). – Kelvin Sep 15 '14 at 21:59
  • @Kelvin that was not my intention. I will update my answer :) – Grzegorz W Sep 16 '14 at 08:59
0

You need to change your sequence if you want to start by 20 rather than 1.

CREATE SEQUENCE "DOGINFO_SEQ" MINVALUE 20 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 20 CACHE 20 NOORDER NOCYCLE;

And it seems you didn't use the right sequence in your trigger.

CREATE OR REPLACE TRIGGER  "BI_DogInfo" 
BEFORE INSERT ON "DogInfo" FOR EACH ROW
WHEN (NEW."Id" IS NULL OR NEW."Id" = 0) BEGIN
SELECT "DOGINFO_SEQ".nextval INTO :NEW."Id" FROM dual;
END;
Mupmup
  • 93
  • 4
-1

I think you using wrong Seq name.

Instead of using DOGINFO_SEQ use USERINFO_SEQ.

slavoo
  • 5,798
  • 64
  • 37
  • 39
posix
  • 1
  • 3