主页 / 问题 / 1120469
Accepted
Александр
Asked:2020-05-05 12:37:50 +0000 UTC2020-05-05 12:37:50 +0000 UTC

如果还没有具有主键的记录，则将记录插入具有外键的表中

  • 772

例如，有两个具有以下结构的表：

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 中插入一条新记录。

但是由于表 B 上的外键，我无法在其中插入一条Id不在表 A 中的记录。

问题是，如何实施？

sql

3 个回答

  1. Best Answer

    如果 triggers 不能省略，例如，因为 insert with已经INSERT实现，那么这是一个带有复合触发器的快速解决方案：

    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
    • 3

  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
    • 3

  3. 如果您不能没有触发器，那么在oracle中可以指定延迟到外键事务结束的完整性检查（关键字DEFERRABLE）。

    示例文章。

    • 1

相关问题