Вставка записи в таблицу с внешним ключом если записи с первичным ключом еще нет

Question

Например, есть две таблицы со следующий структурой:

CREATE TABLE A ( 
    ID number PRIMARY KEY,
    TEXT varchar2(4000)
);
CREATE TABLE B ( 
    ID number PRIMARY KEY REFERENCES A,
    KEY varchar2(256)            
);

На таблицу A созданы два триггера (они приведены в этом вопросе).

Первый триггер генерирует Id для обоих PK. Во втором триггере происходит шифрование текста и шифровка должна заменить пришедший во вставке текст в A, а с ключом шифрования он должен вставлять новую запись в таблицу B.

Но из-за внешнего ключа на таблице В, я не могу вставить в неё запись с Id, которого ещё нет в таблице А.

Вопрос в том, как это можно реализовать?

Answer 1

Если от триггеров нельзя отказатья, например, потому что вставка с INSERT уже реализована, то такое quick'n dirty решение с составным триггером:

create or replace trigger triga for insert on a compound trigger
    key varchar2 (32); 

    before each row is begin 
        :new.id := 1;
        :new.text := 'XXX';
        key := 'KEY';
    end before each row; 

    after each row is begin 
        insert into b values (:new.id, key);
    end after each row; 
end;
/
insert into a values (null, 'new text');

select a.id, a.text, b.key from a join b on b.id=a.id;

        ID TEXT             KEY             
---------- ---------------- ----------------
         1 XXX              KEY

Answer 2

Правильней будет отказаться от триггеров, так как часто в них нет никакой необходимости.
Логику вставки как в вопросе можно тогда реализовать так:

create or replace type textRow force is object (text varchar2 (400), key varchar2 (32))
/
create or replace type textRows is table of textRow; 
/
create or replace procedure insertEncodedTextes (textes textRows) is
    type newIdRows is table of number;
    ids newIdRows; 
    targets textRows := textRows ();
begin
    targets.extend (textes.count);   
    <<encode>> for ix in 1..targets.count loop
        targets(ix) := textRow ('##'||textes(ix).text, '**'||textes(ix).text);
    end loop;

    forall ix in indices of targets
        insert into a (text) values (targets(ix).text)
        returning id bulk collect into ids;   

    forall ix in indices of ids
        insert into b values (ids(ix), targets(ix).key);
end;
/

Вставить пару новых записей и убедится, что результат как и ожидался:

exec insertEncodedTextes (textRows ( -
    textRow ('aaa', null), -
    textRow ('bbb', null)));

select a.id, a.text, b.key from a join b on b.id=a.id;

        ID TEXT             KEY             
---------- ---------------- ----------------
         1 ##aaa            **aaa           
         2 ##bbb            **bbb

Answer 3

Если без триггеров не обойтись, то в oracle есть возможность указать у внешних ключей отложенную до конца транзакции проверку целостности (ключевое слово DEFERRABLE).

Статья для примера.

БЛОГ НА HUSL

Вставка записи в таблицу с внешним ключом если записи с первичным ключом еще нет

Ответы (3 шт):