CODEWITHSUNDEEP
sqloracle-databasetriggersoracle10g
Alex Hope O'Connor
Alex Hope O'Connor

Reputation: 9694

Trigger created with compilation errors

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

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

Upvotes: 6

Views: 36350

Answers (4)

Dylan Hercher
Dylan Hercher

Reputation: 21

First run SHOW ERRORS; to view the issues in your CREATE TRIGGER statement

** moving most helpful comment to a post

Upvotes: 2

Trevor North
Trevor North

Reputation: 2296

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.

Upvotes: 1

Yahia
Yahia

Reputation: 70369

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;

Upvotes: 1

Luke Woodward
Luke Woodward

Reputation: 64959

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 (....) x instead.
  2. Your outermost SELECT doesn't have a FROM clause. Try adding FROM DUAL, since this outermost SELECT isn'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.

Upvotes: 6

Related Questions