I wrote this trigger to discount the top client in the database by 10% when a new purchase is made:
x
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;
/
Advertisement
Answer
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:
- I don’t believe you can do
SELECT (....).CLIENTNO
. TrySELECT x.CLIENTNO FROM (....) x
instead. - Your outermost
SELECT
doesn’t have aFROM
clause. Try addingFROM DUAL
, since this outermostSELECT
isn’t selecting from any tables. - The PL/SQL assignment operator is
:=
, not=
. To assign to:NEW.AMOUNT
, you need to write:NEW.AMOUNT := (:NEW.AMOUNT * 0.1);
. - Multiplying the amount by 0.1 gives the client a 90% discount, not a 10% discount.