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...

问题:

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'

回答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


回答2:

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.

  • 发表于 2019-03-23 18:10
  • 阅读 ( 173 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除