用于在insert语句之前检查值的PL/SQL触发器

Question

我是PL/SQL的新手，我有一个关于我试图实现的触发器的问题。

触发器的目的是在插入到表中之前检查货币值，以查看是否有人在插入过程中犯了错误。如果有，他们将收到一条消息，说明该值不正确。这些值有数十亿，所以现在我只是检查输入的值是否大于10000。

我目前拥有的触发器是；

CREATE TRIGGER Check_Value
BEFORE INSERT OR UPDATE OF "Potential Annual Value By 2026" ON AIPOTENTIALVALUEFORHEALTHCARE
BEGIN
IF (NEW."Potential Annual Value By 2026" < 10000.00) THEN
DBMS_OUTPUT.put_line('Value typed was incorrect');
ELSIF (NEW."Potential Annual Value By 2026" >= 10000.00) THEN
INSERT INTO AIPOTENTIALVALUEFORHEALTHCARE VALUES(NEW.ValueID, NEW.ApplicationID, NEW."Application Name", NEW.KeyDriverForAdoptionID, NEW."KeyDriverDescription", NEW."Potential Annual Value By 2026");
END IF;
END;

由于出现错误，这将不起作用：

PLS-00201: identifier NEW.'Potential Annual Value By 2026' must be declared

我的猜测是我错误地设置了触发器，并且它不知道在运行触发器时要检查哪个值。根据一些研究，我尝试使用.NEW将语句的值传递给触发器，但是我不确定这是否是正确的实现。

我已经尝试了已经发布的方法；

CREATE TRIGGER Check_Value
BEFORE INSERT OR UPDATE OF "Potential Annual Value By 2026" ON AIPOTENTIALVALUEFORHEALTHCARE
BEGIN
  IF (:NEW."Potential Annual Value By 2026" < 10000.00) THEN
    DBMS_OUTPUT.put_line('Value typed was incorrect');
  ELSIF (:NEW."Potential Annual Value By 2026" >= 10000.00) THEN
    INSERT INTO AIPOTENTIALVALUEFORHEALTHCARE VALUES
      (:NEW.ValueID, :NEW.ApplicationID, :NEW."Application Name", 
       :NEW.KeyDriverForAdoptionID, :NEW."KeyDriverDescription", 
       :NEW."Potential Annual Value By 2026");
  END IF;
END;

并收到一个不同的错误：

ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 -  "NEW or OLD references not allowed in table level triggers"
*Cause:    The trigger is accessing "new" or "old" values in a table trigger.
*Action:   Remove any new or old references.

如果此错误指出我无法在表级触发器中使用新引用，那么在提交insert语句之前，我如何能够验证它的内容呢？

Answer 1

NEW关键字和FOR EACH ROW子句前缺少冒号。此外，您不需要(也不能)在触发器中重新发出INSERT，它无论如何都会发生(如果没有引发错误)：

CREATE TRIGGER Check_Value
BEFORE INSERT OR UPDATE OF "Potential Annual Value By 2026" ON AIPOTENTIALVALUEFORHEALTHCARE
FOR EACH ROW
BEGIN
  IF (:NEW."Potential Annual Value By 2026" < 10000.00) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Value typed was incorrect');
  END IF;
END;

我确信这只是一个训练示例，但是DBMS_OUTPUT.PUT_LINE不是一个向用户抛出错误的合适方法，因为它的输出只有在使用像SQL Developer这样的开发工具时才能看到。使用RAISE_APPLICATION_ERROR。此外，它实际上不会引发异常，所以它根本不会阻止插入。

实际上，使用check约束可能会更好地完成此检查-假设列值永远不能低于10000：

ALTER TABLE AIPOTENTIALVALUEFORHEALTHCARE
   ADD CONSTRAINT AIPOTENTIALVALUEFORHEALTHCARE_CHK_VALUE
      CHECK ("Potential Annual Value By 2026" >= 10000);