Доброго времени суток. Имеется Oracle 11.2.0.3 Enterprise Edition.
Задача, использовать последовательность sequence в качестве уникального ключа без пропусков в нумерации.
Вопрос в той или иной степени много раз поднимался (судя по поиску на форуме), но нормальной реализации
я так и не смог найти.

Задача, при insert строки в таблицу test1, при условии что значение поля kl > 5, после commit происходит insert
в таблицу test2, в которую передается значение поля id из таблицы test1. При этом значение поле id в таблице test2
это последовательность, без пропусков, начинается с 1, в конце года обнуляется и с первого января вновь начинается с 1.
Данные в таблицу test2 попадают только через триггер на таблице test1.

В клиент-серверном приложении, откуда пользователи вносят данные, доступа нет, могу ограничения накладывать только через
Oracle. Пользователь иногда вносит данные в таблицу test1, но потом либо связь пропадает, либо он передумывает и кнопку отправить не нажимает,
не знаю что происходит, никак отловить его не могу, но последовательность нарушается в test2 и появляются пропуски, а это очень критично.
В год в таблицу test2 попадает не более 2500 записей.
Подскажите в какую сторону копать?


--Создаем таблицы
create table test1(id number(8), str varchar2(16), kl number(8));
/
create table test2(id number(8), str varchar2(16));
/
--Создаем последовательности
create sequence seq_test1
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
/

create sequence seq_test2
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
/
--Создаем триггеры

create or replace trigger TR_INS_test1
for insert ON test1
compound trigger
tId number(16) :=0;
tKl number(16) :=0;

before statement is
begin
null;
end before statement;

before EACH ROW IS
BEGIN
:New.id := seq_test1.nextval;
END before EACH ROW;

after each row is
begin
tId := :New.id;
tKl := :new.kl;
if (tkl > 5) then
insert into test2(str) values (tId);
end if;
end after each row;

after statement is
begin
null;
end after statement;

end TR_INS_test1;
/
create or replace trigger TR_INS_test2
before INSERT ON test2
FOR EACH ROW
DECLARE
BEGIN
:New.id := seq_test2.nextval;
end TR_INS_test2;

/
--Запросы

insert into test1(str,kl) values ('Test1','7');
commit;
insert into test1(str,kl) values ('Test2','8');
rollback;
insert into test1(str,kl) values ('Test3','1');
commit;
insert into test1(str,kl) values ('Test4','4');
rollback;
insert into test1(str,kl) values ('Test5','8');
commit;
/
--Выводим результат
select * from test1;
select * from test2;

В выборке из test2 2 записи, но поле id должно иметь значения 1 и 2. А не 1 и 3.

dmdmdm,

ваши предложения?

Elic,

max(id) + 1 крайний вариант, и даже если 10 записей в день вставят, то могут быть совпадения.

alter session set isolation_level = serializable куда мне прописать? Сессии пользователей я не контролирую. И задачу они выполняют не одну.

Stax,

Спасибо, но я не потерял двойку, я просто имитировал ситуацию, показывая когда образуются дырки в последовательности. На что поменять, последовательность удобна, раз в год ее обнуляешь и все по новой. Вариант с таблицей вместо последовательности тоже устроит, но как реализовать чтобы не было дырок и дублей?

Прочитал ветку, на которую дали ссылку.

Если бы были проблемы со здоровьем, обратился бы к доктору, а не на форум sql.ru.

В выделенном вами выше сообщении на ваш ответ в другой ветке не вижу ответа по теме.

Вы извините, но все равно решения не увидел.

Большое спасибо. Все работает как нужно.

А как можно хранить дырку? Вообще решение заменить последовательность таблицей с одной строкой полностью устраивает, ее можно и обнулять раз в год, как мне и нужно. Но получается можно использовать и последовательность в моей задаче, с хранением и повторной выдачей дырок. Как это сделать? Для удовлетворения интереса просто.

В общем тему можно закрывать, сделал таблицу-последовательность по годам, надеюсь теперь забуду об этой задаче, но не забуду метод.

create table my_seq_test2(current_valnumber(8),yyyy number(4));
/
create or replace trigger TR_INS_test2
before INSERT ON test2
FOR EACH ROW

DECLARE
v_curval number(8) := 0;
xc integer := 0;
xYear number(4) := to_number(to_char(sysdate, 'yyyy'));
BEGIN
select count(1) into xC from my_seq_test2 where YYYY = xYear;
if (xC > 0) then
update my_seq_test2
set current_val = current_val + 1
where YYYY = xYear
returning current_val into v_curval;
else
insert into my_seq_test2 (current_val, YYYY) values (0, xYear);

update my_seq_test2
set current_val = current_val + 1
where YYYY = xYear
returning current_val into v_curval;
end if;
-- :New.id := seq_test2.currval;
:New.id := v_curval;
end TR_INS_test2;

Понял о чем говорили добрые и злые люди, давая ссылку на другую тему :). Всем спасибо, всем добра. Надеюсь информация будет кому-то еще полезна.