创建触发器时出现编译错误

Question

我编写此触发器是为了在进行新购买时将数据库中的顶级客户折扣 10%：

CREATE or REPLACE TRIGGER CLIENT_DISCOUNT
  BEFORE INSERT
  ON PURCHASE
  FOR EACH ROW
DECLARE
  CLIENTNO  NUMBER(5);
BEGIN
  SELECT (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL FROM CLIENT, 
    (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY CLIENTNO) TOTALS WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO  AND ROWNUM <= 1 ORDER BY TOTALS.TOTAL DESC).CLIENTNO INTO CLIENTNO;
  IF :NEW.CLIENTNO = CLIENTNO THEN
    :NEW.AMOUNT = (:NEW.AMOUNT * 0.1);
  END IF;
END;

但是，当我执行此语句时，我收到此消息：

Warning: Trigger created with compilation errors.

有人可以告诉我我做错了什么吗？

谢谢， 亚历克斯.

更新-错误：

Errors for TRIGGER CLIENT_DISCOUNT:

LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
4/3
PL/SQL: SQL Statement ignored

5/141
PL/SQL: ORA-00907: missing right parenthesis

7/17
PLS-00103: Encountered the symbol "=" when expecting one of the following:


LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
   := . ( @ % ; indicator

8/3
PLS-00103: Encountered the symbol "END"

解决方案：

CREATE or REPLACE TRIGGER CLIENT_DISCOUNT
    BEFORE INSERT
    ON PURCHASE
    FOR EACH ROW
DECLARE
    vCLIENTNO  NUMBER(5);
BEGIN
    SELECT TOPCLIENT.CLIENTNO INTO vCLIENTNO FROM (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY CLIENTNO) TOTALS WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO  AND ROWNUM <= 1 ORDER BY TOTALS.TOTAL DESC) TOPCLIENT;
    IF :NEW.CLIENTNO = vCLIENTNO THEN
        :NEW.AMOUNT := (:NEW.AMOUNT * 0.9);
    END IF;
END;
/

Answer 1

我没有你的表格，所以我不能保证我已经找到了你的所有错误。不过，我可以这么说：

1. 我不相信您可以执行SELECT (....).CLIENTNO。尝试使用 SELECT x.CLIENTNO FROM (....) x 代替。
2. 最外面的 SELECT 没有 FROM 子句。尝试添加 FROM DUAL，因为最外面的 SELECT 不是从任何表中进行选择。
3. PL/SQL 赋值运算符是 :=，而不是 =。要分配给 :NEW.AMOUNT，您需要编写 :NEW.AMOUNT := (:NEW.AMOUNT * 0.1);。
4. 将金额乘以 0.1 将为客户提供 90% 的折扣，而不是 10% 的折扣。

Answer 2

首先运行 SHOW ERRORS; 以查看 CREATE TRIGGER 语句中的问题

** 将最有帮助的评论移至帖子

Answer 3

尝试

CREATE or REPLACE TRIGGER CLIENT_DISCOUNT
  BEFORE INSERT
  ON PURCHASE
  FOR EACH ROW
DECLARE
  vCLIENTNO  NUMBER(5);
BEGIN
  SELECT Z.CLIENTNO INTO vCLIENTNO FROM (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL FROM CLIENT, 
    (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY CLIENTNO) TOTALS WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO  AND ROWNUM <= 1 ORDER BY TOTALS.TOTAL DESC) Z;
  IF :NEW.CLIENTNO = vCLIENTNO THEN
    :NEW.AMOUNT := (:NEW.AMOUNT * 1.091);
  END IF;
END;

Answer 4

除了其他人发现的语法错误之外，您很可能会遇到变异触发器，因为您无法从要插入的表中进行选择。

如果您可以更改数据模型，则可能需要识别另一个表中的顶级客户，而不是在插入该表时尝试选择购买金额总和表。