sql - SQL-触发 “not in”

Question

我正在尝试在配音数据库中触发时间。我想检查一下在不出现角色的电影配音中不能对角色进行配音。这是PDM:

和CDM

我是SQL的初学者，但我知道表“DUBBES”中应该有一些触发器。我试图做这样的事情，但是我收到一条消息，指出触发器无效:

CREATE OR REPLACE TRIGGER x_character
BEFORE INSERT OR UPDATE ON dubbes FOR EACH ROW
DECLARE

  IF (  :NEW.CHAR_id_character NOT IN ( SELECT CHAR_id_CHARACTER
                                          FROM APPEARS
                                         WHERE APPEARS.MOV_id_movie = (SELECT dubbing.Mov_id_movie
                                                                         FROM DUBBING
                                                                        WHERE dubbing.id_dubbing = :NEW.dab_id_dubing)))
  THEN
    RAISE_APPLICATION_ERROR(-20000, 'Character is not in this movie.');
  END IF;

END;
/

我真的很感谢您的帮助。
提前谢谢了!

Answer 1

我认为您的代码应如下所示:

create or replace trigger X_character BEFORE INSERT OR UPDATE ON dubbes
FOR EACH ROW
DECLARE  
  haveit number;
  idmovie number;
begin

  select dubbing.Mov_id_movie into idmovie from DUBBING where dubbing.id_dubbing = :new.dab_id_dubing;

  select count(*) into haveit from APPEARS
  where 
  APPEARS.MOV_id_movie = idmovie and
  APPEARS.CHAR_id_CHARACTER = :new.CHAR_id_character;

  IF( haveit = 0 ) then
    RAISE_APPLICATION_ERROR(-20000, 'Character is not in this movie.');
  END IF;


END;
/