Trigger oracle: whats wrong?

Question

This is my code:

create trigger TriggerAdresse1
    before INSERT
    on Adresse
    for each row
declare
    enthaelt boolean;
begin
    if ((Provinz in (select Name from Provinz2)) and (Laendercode in (select Laendercode from Provinz2))) then
        enthaelt := true;
    end if;
    if(enthaelt:=false) then
        rollback;
    end if;
end;

I am trying to cancel the insert if the attribute Provinz or Laendercode isn´t in the table Provinz2. Datagrip says it´s not valid...

Thanks for your help! Best regards

Answer 1

• A BOOLEAN value in Oracle can have three values: TRUE, FALSE and NULL, you did not initialize the variable.
• You have to refer to new values (by default with :new)
• By default a trigger cannot contain COMMIT or ROLLBACK

Your code must be this:

create trigger TriggerAdresse1
    before INSERT
    on Adresse
    for each row
declare
    enthaelt INTEGER;
begin

    SELECT COUNT(*)
    INTO enthaelt 
    FROM Provinz2
    WHERE Name = :new.Provinz
       AND Laendercode = :new.Laendercode;
    if enthaelt = 0 then
        RAISE_APPLICATION_ERROR(-20001, 'Provinz oder Ländercode ungültig');
    end if;
end;

However, your requirement should be better implemented with FOREIGN KEY Constraint

alter table Adresse add constraint Provinz_FK FOREIGN KEY (Provinz) 
  references Provinz2 (Name);

alter table Adresse add constraint Laendercode_FK FOREIGN KEY (Laendercode) 
  references Provinz2(Laendercode);

Most likely Laendercode is not a UNIQUE key, but Name+Laendercode is. Then it would be this:

alter table Adresse add constraint Provinz_FK FOREIGN KEY (Provinz, Laendercode) 
  references Provinz2 (Name, Laendercode);