Unanswered: ID with auto_increment is getting too big
In all my tables i've made the ID with an auto_increment:
CREATE SEQUENCE "S_TAGS_ID"
START WITH 1
INCREMENT BY 1
CREATE OR REPLACE TRIGGER "SYSTEM"."TR_TAGS_ID"
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
IF(:new.ID IS NULL) then
I've made a script with much (50000) inserts, but 80% of the inserts in this table offend a unique key constraint. so finally i got only 500 "real" inserts in the table.
Now the problem is that the before_insert_trigger always calls the nextval of the sequencer, so the value of the sequencer does increase with 50000, though i really only insert 500 tags.
How can i fix it that when a insert is cancelled, the sequencer doesn't increase (without using a merge into when not exists instead of a usual insert)