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;
/
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:
SELECT (....).CLIENTNO
. Try SELECT x.CLIENTNO FROM (....) x
instead.SELECT
doesn't have a FROM
clause. Try adding FROM DUAL
, since this outermost SELECT
isn't selecting from any tables.:=
, not =
. To assign to :NEW.AMOUNT
, you need to write :NEW.AMOUNT := (:NEW.AMOUNT * 0.1);
.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With