sql - Oracle Trigger PLS-00103. Query with multiple rows in temporal variable

Question

I'm trying to write a trigger that raises error when someone attempts to rate a product which has not been bought. I have come up with a query to get the purchase history of a client :

SELECT nref 
FROM CartClient a 
INNER JOIN PaidCart b 
     ON a.idpurchase = b.idpurchase
INNER JOIN CartDetails c  
     ON b.idpurchase = c.idpurchase 
WHERE a.Id = '12345672X'

which works fine.

So the next trigger should check if the product of a new rating (:new.NRef) has not been bought, namely is not part of the result of the last query (NOT IN).

CREATE OR REPLACE TRIGGER cant_rate
BEFORE INSERT ON Rating
FOR EACH ROW   
    BEGIN
        IF (:new.NRef NOT IN (SELECT nref FROM CartClient a 
                                   INNER JOIN PaidCart b 
                                      ON a.idpurchase = b.idpurchase
                                   INNER JOIN CartDetails c  
                                      ON b.idpurchase = c.idpurchase 
                                   WHERE a.Id =:new.Id)) THEN
            RAISE_APPLICATION_ERROR(-20603,'Cant rate a not bought product');
        END IF;
    END;

I get error:

"PLS-00103:Encountered the symbol “INNER” when expecting one of".

I have tried to store the result of the query in a temporal variable using SELECT INTO. But, it's a multiple row result. What could I do?

question from:https://stackoverflow.com/questions/65850019/oracle-trigger-pls-00103-query-with-multiple-rows-in-temporal-variable

Answer 1

How about such an approach?

CREATE OR REPLACE TRIGGER cant_rate
  BEFORE INSERT ON Rating
  FOR EACH ROW   
DECLARE
  l_exists NUMBER(1) := 0;
BEGIN
  SELECT MAX(1)
    INTO l_exists
    FROM dual
    WHERE EXISTS (SELECT nref FROM CartClient A 
                  INNER JOIN PaidCart b 
                     ON A.idpurchase = b.idpurchase
                  INNER JOIN CartDetails C  
                     ON b.idpurchase = C.idpurchase 
                  WHERE A.ID = :NEW.ID
                    AND a.nref = :NEW.nref           --> is it "a.nref"?
                 );
  IF l_exists = 0 THEN
     RAISE_APPLICATION_ERROR(-20603,'Cant rate a not bought product');
  END IF;
END;

Note remark "is it a.nref?" - you never said which table owns that nref column so I presumed it is cartclient; modify it, if necessary.

---

As of your attempt: if you executed it in SQL*Plus or SQL Developer, you'd see a message regarding subquery in IF; something like this:

LINE/COL  ERROR
--------- -------------------------------------------------------------
2/3       PL/SQL: Statement ignored
2/21      PLS-00405: subquery not allowed in this context

So, no - you can't do it that way.