如何使用回傳多個值的子查詢撰寫正確的觸發器

我需要創建一個觸發器，而不是在視圖中插入一行，而是在 3 個表中插入值。這是正確的觸發器：

 INSTEAD OF INSERT ON Retete_vegetariane
 FOR EACH ROW
BEGIN
    INSERT INTO categorie(categ_id, tip)
    VALUES(:NEW.categ_id, :NEW.tip);

    INSERT INTO Ingredient(ingred_id, ingredient)
    VALUES(:NEW.ingred_id, :NEW.ingredient);

    INSERT INTO Reteta(reteta_id, nume, descriere, categ_id, vegetariana, timp_preparare, portii)
    VALUES(:NEW.reteta_id, :NEW.reteta, :NEW.descriere, :NEW.categ_id,'D', :NEW.timp_preparare, :NEW.portii);
    
    INSERT INTO Set_Ingrediente(reteta_id, ingred_id, cantitate, um, comentarii)
    VALUES (:NEW.reteta_id, :NEW.ingred_id, :NEW.cantitate, :NEW.um, :NEW.comentarii);
END;
/

現在，如果 reteta_id 已存在，我想更新觸發器以引發應用程式錯誤。它仍然提出一個錯誤，說 reteta_id 是一個主鍵，但現在我想讓它說它已經存在。這是我試過的代碼，但它說你不能在觸發器中使用這樣的子查詢。

CREATE OR REPLACE TRIGGER ad_vegetarian
 INSTEAD OF INSERT ON Retete_vegetariane
 FOR EACH ROW
DECLARE 
 variabil reteta.reteta_id%type;
BEGIN
variabil:=:NEW.reteta_id;
 IF variabil EXISTS(
    SELECT DISTINCT reteta_id FROM reteta;) THEN
    RAISE_APPLICATION_ERROR(-20512,'Reteta_id  already exists');
END IF;
    INSERT INTO categorie(categ_id, tip)
    VALUES(:NEW.categ_id, :NEW.tip);
    INSERT INTO Ingredient(ingred_id, ingredient)
    VALUES(:NEW.ingred_id, :NEW.ingredient);
    INSERT INTO Reteta(reteta_id, nume, descriere, categ_id, vegetariana, timp_preparare, portii)
    VALUES(:NEW.reteta_id, :NEW.reteta, :NEW.descriere, :NEW.categ_id,'D', :NEW.timp_preparare, :NEW.portii);
    INSERT INTO Set_Ingrediente(reteta_id, ingred_id, cantitate, um, comentarii)
    VALUES (:NEW.reteta_id, :NEW.ingred_id, :NEW.cantitate, :NEW.um, :NEW.comentarii);
END;/

uj5u.com熱心網友回復：

將子查詢移出IF：

...
begin
  select nvl(max(1), 0)
  into variabil
  from dual 
  where exists (select null
                from reteta
                where reteta_id = :new.reteta_id
               );

  if variabil = 1 then
     raise_application_error(-20512, 'Reteta_id already exists');
  end if;  
  ...             

uj5u.com熱心網友回復：

如果您查看 的檔案IF，您會發現它boolean_expression在 之后接受IF。boolean_expression定義在conditional_predicate分支中列出了這些替代方案：

{ collection.EXISTS ( index )
| expression { IS [ NOT ] NULL
             | [ NOT ] { BETWEEN expression AND expression
                       | IN ( expression [, expression ]... )
                       | LIKE pattern
                       }
             | relational_operator expression
             }
| { named_cursor | SQL } % { FOUND | ISOPEN | NOTFOUND }
}

它不允許您放置EXISTS謂詞，因為它是一個 SQL 謂詞。

您可以避免雙重檢查（在選擇和后續插入時）的存在：執行insert和處理dup_val_on_index例外。

create table t1 (
  id1 int primary key,
  val1 varchar2(100)
)

create table t2 (
  id2 int primary key,
  val2 varchar2(100),
  id1 references t1(id1)
)

create view v_test as
select *
from t1
  join t2
  using(id1)

create trigger trg_test
instead of insert on v_test
for each row
begin
  insert into t1(id1, val1)
  values (:new.id1, :new.val1);
  
  begin
    insert into t2(id2, val2, id1)
    values(:new.id2, :new.val2, :new.id1);
  exception
    when dup_val_on_index then
      RAISE_APPLICATION_ERROR(-20512,'Reteta_id  already exists');
  end;
end;
/

insert into v_test(id1, val1, id2, val2)
values(1, 'A', 1, 'B')

1 行受影響

insert into v_test(id1, val1, id2, val2)
values(2, 'AA', 1, 'B')

ORA-20512: Reteta_id 已經存在
ORA-06512：在“FIDDLE_CPKPBNEZVLNFZLUOGPNZ.TRG_TEST”，第 10 行
ORA-04088: 執行觸發器 'FIDDLE_CPKPBNEZVLNFZLUOGPNZ.TRG_TEST' 期間出錯

db<>在這里擺弄

標籤：sql 甲骨文 触发器

上一篇：在SQL中使用PIVOT和UNPIVOT進行資料重組

下一篇：如何獲取位于Oraclesql表中列內的JSON中的值？