Update or Insert и FireBird 2.5.2

[Pashka_cool]

Коллеги, выручайте. Не работает "Update or Insert" команда, точнее при ситуации INSERT-а не отрабатывает триггер "BEFORE INSERT" по таблице, который берёт значение ключа из геренатора и вторая команда уже выбивает ошибку нарушения уникальности записей.
Попытки выполнить задачу через MERGE вообще неуспешны, т.к. MERGE не работает, когда в условии USING указана эта же таблица. Перебрал кучу вариантов - результат нулевой. Вот код добавления:

  update or INSERT into a_ftypes (ftname, ftdescr, ft_id) VALUES('DEFAULT',        '', 0) matching (ft_id) returning a_ftypes.indexid into :var_index;
  update or INSERT into a_ftypes (ftname, ftdescr, ft_id) VALUES('USER INFO',      '', 1) matching (ft_id) returning a_ftypes.indexid into :var_index;
  update or INSERT into a_ftypes (ftname, ftdescr, ft_id) VALUES('TIME DIAPOSONE', '', 2) matching (ft_id) returning a_ftypes.indexid into :var_index;

Вторая строка выбивает. Упоминать в перечисленных полях индексное вместе со значением из генератора - работает, но будет всегда менять значение индекса, что абсурдно.
Кстати пробую выполнять этот код в ХП через IBExpert 2013.6.29.1

[Stilet]

но будет всегда менять значение индекса, что абсурдно

В каком смысле? Поясни эту фразу.

Кстати, я лично в FB предпочитаю такую конструкцию:

INSERT into a_ftypes (ftname, ftdescr, ft_id) VALUES('DEFAULT',        '', (select max(ft_id)+1 from a_ftypes))

По крайней мере она меня никогда не подводила в отличии от генераторов.

[Pashka_cool]

Дело в том, что приведённый код ниже вызывается на регулярной основе. Я лишь в нём значения явно задал, но будут на самом деле параметры. Некоторые из параметров тоже уникальны, как и первичный ключ. Если код вызвать дважды, то второй раз сработает UPDATE, а не INSERT, что вполне соответствует инструкции, но при обновлении будет и значение ключа подставлено уже новое, т.к. оно из генератора получается - я т.к. ссылочная целостность никем не отменена, то зависимые записи в другой таблице просто не позволят сделать такой UPDATE.
При этом использовать вместо генератора поиск максимального значения - нехороший подход. Если система многопользовательская (отмечу, что коилчество одновременных сессий - несколько сотен), то может быть получено одинаковое значение ключа (так и было у меня на заре разработок). Генератор эту ситуацию исключает.

Смысл прост, если указать явно вставку значения ключа, типа:
update or INSERT into a_ftypes (indexid, ftname, ftdescr, ft_id) VALUES(next value for gen_a_ftypes_id, 'DEFAULT','', 0) matching (ft_id) returning a_ftypes.indexid into :var_index;
то после второго вызова этой же операции произойдёт уже не INSERT, а UPDATE, что соответствует инструкции, но... будет обновлено значение первичного ключа из генератора на новое. А это нарушит ссылочную целостность таблиц. Триггер на пред-инсерт должен был разрулить это автоматом (т.е. как указано в первом сообщении значение ключа не упоминать в полях и параметрах). Но не работает. Хотя при передаче значения NULL он бы работал! Но тогда UPDATE не допускает такую запись.
А упомянутый вам вариант с поиском максимума и по времени будет долог при ёмкой таблице и ошибки с ним пойдут, когда количество сессий будет в несколько сотен одновременно (я это уже давно проходил, больше не посторяю эту ошибку).

[Vapaamies]

Мне кажется, что в values на место ft_id нужно написать что-то вроде:

  case when ft_id is null then next value for ge_a_ftypes_id else ft_id end

Либо, если в Firebird есть функция-макрос типа nvl в Oracle, использовать ее:

  nvl(ft_id, next value for ge_a_ftypes_id)

[Stilet]

Хм... Если честно мне кажется что сам механизм работы с БД продуман плохо...
Такие телодвижения с триггерами... Не слабо, так сказать.
В общем у меня нет идей, кроме того что сказал выше. Нужно анализировать твою разработку - зачем понадобилась такая сложность.

[Vapaamies]

С чашкой вечернего чая я тоже понял, что теперь не понимаю, чего же хочет автор. Присоединяюсь к Stilet-у, хотелось бы услышать изначальную задачу.

[Pashka_cool]

Ок, повторюсь с задачей, но более разборчиво:
Есть мастер-таблица и 10 таблиц-подчинённых к ней (по ходу кол-во не важно, просто указываю, как есть).
Есть общая процедура добавления записи во все эти таблицы. Но при первой итерации не во все подчинённые таблицы будет внесена запись, а лишь в часть из них. При второй итерации (обновления уже) в мастере уже запись есть. В части подчинённых - тоже, но в ещё некоторых подчинённых таблицах нет и только в этот (второй раз) будут вноситься данные, т.е. в них будет INSERT,а в остальные уже UPDATE. К примеру: при регистрации пользователь внёс только часть данных обязательных, а позже решил внести дополнительные + подредактировать уже имеющиеся. Так вот конструукция UPDATE or INSERT тут как раз и хороша, но с ней проблема в работе с уникальными ключами записей. Совершенно ничего сложного не происходит как раз. ЗАдача из разряда естественных. Поэтому в общей процедуре находиться вся пачка этих UPDATE or INSERT по каждой и таблиц-подчинённых.
Да вы сами попробуйте выполнить дважды одну и туже команду эту и увидите проблему. Напишите команду 2 раза подряд (эквивалет вызвать её дважды). И проблема тут же на лицо с уникальным ключом.
Если его передавать, как null, то ошибка пре-компиляции из-за UPDATE, если указать генератор, то повторная сработка сменит уникальный ключ на новое значение. Если не указывать вообще поле уникального ключа в этом операнде, то два подряд INSERT-а не работают (хотя триггер должен отработать, но нет, не работает)! На втором INSERT-е сразу ошибка нарушения уникальности по ключу.
Я лишь полагаю, что никто не использовал этот операнд подряд много раз, поэтому проблему и не уловил. Да и MERGE вообще не работает в этой же ситуации. Все примеры MERGE в Инете используют в качестве USING выражение по другой таблице, а не по той же, в которую этот операнд должен выбрать INSERT или UPDATE. НЕт таких рабочих примеров! Засада какая-то, блин.
По части NVL, то в FireBird нет эквивалента. C ORACLE я порядочно поработал, потому в курсе, о чём речь. По части использовать case - дело в том, что я не передаю в эту процедуру значение поля индексного. Потому CASE-у не с чем оперировать.

[Stilet]

при первой итерации не во все подчинённые таблицы будет внесена запись

Стесняюсь спросить, почему?

сами попробуйте выполнить дважды одну и туже команду эту и увидите проблему.

Вот тот способ что я описал выше таких проблем мне никогда не давал.
Я именно так дописываю недостающие данные в таблицы.
Сначала предположим проделываю несколько UPDATE по условию чтоб отредактировать то что уже имеется, если такого нет, редактирование не сработает, а потом инсертами добавляю.

[Аватар]

Зачем update or INSERT в таблицу, в которой значение ключа триггером генерируется? Эта конструкция для таблиц, в которых это значение программно создается и не более того

[Pashka_cool]

Одним словом я вам, коллеги, задал вопрос о нерабочих двух конструкциях в FireBird-е, которые должны работать, но заставить их работать правильно в моей ситуации не выходит (хотя ситуация проста и инструкции явно в чём то недопилены). Пусть так. Тогда упрощу задачу до немогу. Просто задам вопрос:
Есть таблица, в ней 1 000 000 записей. Мне нужно внести или обновить ещё одну. Я не знаю, есть она или её ещё нет в таблице. Как поступать? Искать её на наличие, а по результату уже выполнять UPDATE или INSERT? И какова будет скорость Базы при частых таких запросах?
Именно поэтому я пытался прибегнуть к упомянутым двум видам инструкций и получил облом. Как сделать "классически" - я прекрасно понимаю. Но скорость хорошую это не даст, когда записей несколько лимонов и одновременных рабочих сессий несколько сотен!