SQL 使用编译错误创建的触发器

Trigger created with compilation errors

提问by Alex Hope O'Connor

I wrote this trigger to discount the top client in the database by 10% when a new purchase is made:

我编写了这个触发器来在进行新购买时将数据库中的顶级客户折扣 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;

However when i execute this statement i receive this message:

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

Warning: Trigger created with compilation errors.

Can someone please tell me what I am doing wrong?

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

Thanks, Alex.

谢谢，亚历克斯。

UPDATE - 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"

Solution:

解决方案：

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

回答by Luke Woodward

I don't have your tables to hand so I can't guarantee that I've found all your errors. However, I can say the following:

我手头没有你的桌子，所以我不能保证我已经找到了你所有的错误。但是，我可以说以下几点：

1. I don't believe you can do SELECT (....).CLIENTNO. Try SELECT x.CLIENTNO FROM (....) xinstead.
2. Your outermost SELECTdoesn't have a FROMclause. Try adding FROM DUAL, since this outermost SELECTisn't selecting from any tables.
3. The PL/SQL assignment operator is :=, not =. To assign to :NEW.AMOUNT, you need to write :NEW.AMOUNT := (:NEW.AMOUNT * 0.1);.
4. Multiplying the amount by 0.1 gives the client a 90% discount, not a 10% discount.

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% 的折扣。

回答by Yahia

try

尝试

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;

回答by Trevor North

Besides the syntax errors identified by others, most likely you are getting a mutating trigger as you cant select from the table you are inserting into.

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

If you can change the data model, might be you need to identify the top client in another table rather than trying to select the sum of amounts purchase table when you are also inserting into that table.

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