Comparing :new value inserted with a trigger

Question

I'm trying to build a trigger that checks if the row that is gonna be inserted, exists in another table.

Basically my 2 tables share one column, ID. I want to prevent the insertion when the new row doesnt exist at least once in the other table.

I have this:

create or replace trigger BIM
before insert on TABLE1 
for each row
begin
    if not exists (select 1 from TABLE2 where TABLE2.ID = :new.TABLE1.ID)
then
    raise_application_error(-20634, 'Error');
  end if;
end;

But i'm getting this:

PLS-00049: bad bind variable 'NEW.TABLE1'

Answer 1

Gordon is right, It is preferable to use Foreign Key constraint for this scenario.

The problem with your code ( apart from the error which Gordon pointed out )is that unlike few other DBMS like Postgres, In Oracle you cannot use EXISTS in a PL/SQL expression/statements like IF. It should be a purely SQL statement.

create or replace trigger BIM
before insert on TABLE1 
 for each row
declare 
l_id_exists INT;
begin
    select CASE WHEN 
                 exists (select 1 from TABLE2 where TABLE2.ID = :new.ID) 
             THEN 1 
        ELSE 0 END INTO l_id_exists from dual;
   if l_id_exists = 0
   then
    raise_application_error(-20634, 'Error');
  end if;
end;
/

DEMO