触发器中的PL/SQL比较错误(PLS-00405) [英] PL/SQL Comparison error in trigger (PLS-00405)

问题描述

我正在尝试制作一个简单的触发器，该触发器必须在插入和更新时检查给定的值.我必须检查ID是否与NAVAZUJE_NA(外键引用ID)不同.这是代码:

I'm trying to make a simple trigger which would have to check the given values on insert and update. I have to check that ID has to different of NAVAZUJE_NA (Foreign key referencing ID). Here's the code:

CREATE OR REPLACE TRIGGER PREDMETY_INSERT_TRIGGER 
BEFORE INSERT OR UPDATE ON PREDMETY 
FOR EACH ROW
BEGIN
  IF :NEW.ID = :NEW.NAVAZUJE_NA THEN --There is an error (PLS-00405) 
    RAISE_APPLICATION_ERROR(-20000, 'Predmet nemuze navazovat sam na sebe.', FALSE);
  ELSIF :NEW.NAVAZUJE_NA > (SELECT MAX(ID) FROM PREDMETY) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Predmet nemuze navazovat na neexistujici predmet.', FALSE);
  ELSIF :NEW.ID < 0 OR :NEW.NAVAZUJE_NA < 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Neplatny index predmetu nebo predmetu, na ktery ma dany predmet navazovat.', FALSE);
  END IF;
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('[ERROR] '||sqlerrm);
END;

感谢您提出任何解决方案.

Thanks for any solution.

推荐答案

当您将max id提取到变量中并在if语句中使用变量时，它会起作用.

It works when you fetch max id into variable and use variable in your if statement.

CREATE OR REPLACE TRIGGER PREDMETY_INSERT_TRIGGER 
BEFORE INSERT OR UPDATE ON PREDMETY 
FOR EACH ROW
declare 
c number;
BEGIN
  SELECT MAX(ID) into c FROM PREDMETY;
  IF :NEW.ID = :NEW.NAVAZUJE_NA THEN --There is an error (PLS-00405) 
    RAISE_APPLICATION_ERROR(-20000, 'Predmet nemuze navazovat sam na sebe.', FALSE);
  ELSIF :NEW.NAVAZUJE_NA > c THEN
    RAISE_APPLICATION_ERROR(-20001, 'Predmet nemuze navazovat na neexistujici predmet.', FALSE);
  ELSIF :NEW.ID < 0 OR :NEW.NAVAZUJE_NA < 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Neplatny index predmetu nebo predmetu, na ktery ma dany predmet navazovat.', FALSE);
  END IF;
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('[ERROR] '||sqlerrm);
END;

这篇关于触发器中的PL/SQL比较错误(PLS-00405)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！