Trigger created with compilation errors
Solution 1
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.
Solution 2
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.
Solution 3
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;
Alex Hope O'Connor
Software Engineer working for DSITIA in Brisbane Australia.
Updated on August 29, 2020Comments
-
Alex Hope O'Connor over 3 years
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; /