CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggersdatabase-trigger
Keka Bron
Keka Bron

Reputation: 439

Comparing :new value inserted with a trigger

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'

Upvotes: 0

Views: 54

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You don't need to repeat the table name:

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

That said, this is an odd requirement. I would recommend that you use a foreign key constraint, but you explicitly say "at least once". That leads me to suspect that you have a bad data model -- you are missing some sort of entity where the id would be the primary key of that table.

Upvotes: 1

Related Questions