How to write a correct trigger with a subquery which is returning more than one value-CodePudding

I need to create a trigger that instead of inserting in a view a row inserts the values in 3 tables. This is the correct trigger:

 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;
/

Now I want to update a trigger to raise an application error if the reteta_id already exists. It still was raising an error saying that reteta_id is a primary key, but now I want it to say that it already exists. This is the code that i tried but it says that you cant use a subquery like this in a trigger.

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;/

CodePudding user response：

Move subquery out of 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;  
  ...

CodePudding user response：

If you check the documentation for IF, you'll find that it accepts boolean_expression after IF. boolean_expression definition lists these alternatives in conditional_predicate branch:

{ 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 }
}

It doesn't allow you to put EXISTS predicate, because it is a SQL predicate.

You may avoid double check (on select and on subsequent insert) of existence: perform insert and handle dup_val_on_index exception.

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 rows affected

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

ORA-20512: Reteta_id  already exists
ORA-06512: at "FIDDLE_CPKPBNEZVLNFZLUOGPNZ.TRG_TEST", line 10
ORA-04088: error during execution of trigger 'FIDDLE_CPKPBNEZVLNFZLUOGPNZ.TRG_TEST'

db<>fiddle here