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

s4chpxco 于 2022-11-03 发布在 Oracle

关注(0)|答案(4)|浏览(461)

我编写了这个触发器，在进行新的购买时，对数据库中的顶级客户端进行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.

有人能告诉我我做错了什么吗？
谢谢你，亚历克斯。
UPDATE -错误：

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;
/

oracle

来源：https://stackoverflow.com/questions/7818276/trigger-created-with-compilation-errors

4条答案

按热度按时间

ldioqlga1#

我手上没有你们的表格，所以我不能保证我找到了你们所有的错误。但是，我可以说以下几点：
1.我不相信你能做SELECT (....).CLIENTNO。试试SELECT x.CLIENTNO FROM (....) x。
1.您的最外层SELECT没有FROM子句。请尝试新增FROM DUAL，因为这个最外层的SELECT并未从任何数据表选取。

PL/SQL指派运算子是:=，而不是=。若要指派给:NEW.AMOUNT，您必须写入:NEW.AMOUNT := (:NEW.AMOUNT * 0.1);。
1.将金额乘以0.1可为客户提供90%的折扣，而不是10%的折扣。

赞(0）回复(0）举报 2022-11-03

yzckvree2#

尝试

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;

赞(0）回复(0）举报 2022-11-03

hiz5n14c3#

除了其他人指出的语法错误之外，最有可能的是，您得到了一个变异触发器，因为您无法从要插入的表中进行选择。
如果您可以更改数据模型，则可能需要在另一个表中标识顶级客户，而不是在插入到该表时尝试选择购买金额总和表。

赞(0）回复(0）举报 2022-11-03

rkue9o1l4#

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

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

赞(0）回复(0）举报 2022-11-03