PLSQL: syntax problems with trigger

Question

I've already created a good few similar triggers and so I'm used to spotting the usual sntax errors - forgetting an END IF or a THEN, for example. I cannot get this one to compile

CREATE OR REPLACE TRIGGER SOLD
BEFORE UPDATE OF STATUS ON STORE_COPY
FOR EACH ROW
DECLARE 
RENT_OR_SALE NVARCHAR2(6);
UPDATED_DVD_ID NUMBER(10);
BEGIN
UPDATED_DVD_ID := SELECT DVD_ID FROM STORE_COPY WHERE :NEW.STATUS != :OLD.STATUS;
RENT_OR_SALE := SELECT (SELECT RENT_OR_SALE FROM DVD JOIN STORE_COPY ON STORE_COPY.DVD_ID =     DVD.DVD_ID WHERE :NEW.STATUS != :OLD.STATUS) INTO RENT_OR_SALE FROM DUAL;
IF :NEW.STATUS != :OLD.STATUS
THEN
IF :OLD.STATUS = 'Y'
THEN
IF :NEW.STATUS = 'N'
THEN
IF RENT_OR_SALE = 'S'
THEN
INSERT INTO SOLD VALUES(NULL, CURRENT_TIMESTAMP, (SELECT PRICE FROM DVD JOIN STORE_COPY ON       STORE_COPY.DVD_ID = DVD.DVD_ID WHERE :NEW.DVD_IS = UPDATED_DVD_ID), :NEW.DVD_ID);
END IF;
END IF;
END IF;
END IF;
END;
/

The terminal gives me this info from the 'show errors' command:

Warning: Trigger created with compilation errors.

SQL> show errors;
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/23     PLS-00103: Encountered the symbol "SELECT" when expecting one of
 the following:
 ( - + case mod new not null <an identifier>
 <a double-quoted delimited-identifier> <a bind variable>
 continue avg count current exists max min prior sql stddev
 sum variance execute forall merge time timestamp interval
 date <a string literal with character set specification>
 <a number> <a single-quoted SQL string> pipe
 <an alternatively-quoted string literal with character set
 specification>
 <an alternat

LINE/COL ERROR
-------- -----------------------------------------------------------------

6/5  PLS-00103: Encountered the symbol "RENT_OR_SALE"

score 2 · Answer 1 · edited May 23 '17 at 10:25

2

You cannot use "select" clause inside IF checking condition. Use select INTO variable and then check this variable in IF (). See examples at: if (select count(column) from table) > 0 then

edited May 23 '17 at 10:25

Community

1
1

answered Mar 11 '14 at 19:41

rtbf

1,509
1
16
35

The 'show errors' shown was actually for the code above (edited). I accidentally posted an earlier version the first time. Sorry. It doesn't have the problem you mentioned, but still doesn't work. – COOLBEANS Mar 11 '14 at 19:49

score 1 · Answer 2 · answered Mar 11 '14 at 20:03

1

So remove ";" from:

:NEW.STATUS != :OLD.STATUS;),

answered Mar 11 '14 at 20:03

rtbf

1,509
1
16
35

I've edited the code again to reflect your suggestions. Thanks. There are only two errors left. Better :-) – COOLBEANS Mar 11 '14 at 20:20
These lines were the main offender: SELECT (SELECT DVD_ID FROM STORE_COPY WHERE :NEW.STATUS != :OLD.STATUS) INTO UPDATED_DVD_ID FROM DUAL; SELECT (SELECT RENT_OR_SALE FROM DVD JOIN STORE_COPY ON STORE_COPY.DVD_ID = DVD.DVD_ID WHERE DVD.DVD_ID = UPDATED_DVD_ID) INTO RENT_OR_SALE FROM DUAL; – COOLBEANS Mar 11 '14 at 22:12
I felt there was something wrong but it was too late too to me investigate it ;-) – rtbf Mar 12 '14 at 06:46

score 1 · Accepted Answer · answered Mar 11 '14 at 22:39

You need to use SELECT...INTO.... There is no variable := SELECT... in PL/SQL. A starting point would be:

CREATE OR REPLACE TRIGGER SOLD
  BEFORE UPDATE OF STATUS ON STORE_COPY
  FOR EACH ROW
DECLARE 
  strRENT_OR_SALE NVARCHAR2(6);
  nUPDATED_DVD_ID NUMBER(10);
BEGIN
  SELECT DVD_ID
    INTO nUPDATED_DVD_ID
    FROM STORE_COPY
    WHERE :NEW.STATUS != :OLD.STATUS;

  SELECT RENT_OR_SALE
    INTO strRENT_OR_SALE
    FROM DVD
    JOIN STORE_COPY
      ON STORE_COPY.DVD_ID = DVD_ID
    WHERE :NEW.STATUS != :OLD.STATUS;

  IF :NEW.STATUS != :OLD.STATUS THEN
    IF :OLD.STATUS = 'Y' THEN
      IF :NEW.STATUS = 'N' THEN
        IF RENT_OR_SALE = 'S' THEN
          INSERT INTO SOLD VALUES
            (NULL,
             CURRENT_TIMESTAMP,
             (SELECT PRICE
                FROM DVD
                JOIN STORE_COPY
                  ON STORE_COPY.DVD_ID = DVD.DVD_ID
                WHERE :NEW.DVD_IS = UPDATED_DVD_ID),
             :NEW.DVD_ID);
        END IF;
      END IF;
    END IF;
  END IF;
END;

But even after all this, you're still going to get errors. I doubt those SELECT's are singleton (that is, they probably return more than one row) so they're going to pitch a fit at runtime. And even when you're done with THAT, you're STILL going to get an ORA-04091 error (Table is mutating; trigger cannot see it) because you've got SELECT statement in this trigger that read the table that the trigger is on.

Are the values that you're trying to read in those SELECT FROM STORE_COPY... statements supposed to be the same one that have just been modified? If so, they're available as the :OLD and :NEW values and thus the following might work:

CREATE OR REPLACE TRIGGER SOLD
  BEFORE UPDATE OF STATUS ON STORE_COPY
  FOR EACH ROW
DECLARE 
  strRENT_OR_SALE NVARCHAR2(6);
  nPrice          NUMBER;
BEGIN
  SELECT RENT_OR_SALE,
         PRICE
    INTO strRENT_OR_SALE,
         nPrice
    FROM DVD d
    WHERE d.DVD_ID = :NEW.DVD_ID

  IF :OLD.STATUS = 'Y' AND
     :NEW.STATUS = 'N' AND
     strRENT_OR_SALE = 'S'
  THEN
    INSERT INTO SOLD VALUES
      (NULL,
       CURRENT_TIMESTAMP,
       nPrice);
  END IF;
END TRIGGER_SOLD;

I may have guessed wrong on where some of the fields (e.g. I guessed that PRICE was on DVD. If it's actually on STORE_COPY then you can probably use :NEW.PRICE instead).

Share and enjoy.

I didn't fully understand your code BUT IT WORKS! I don't understand how this line of code doesn't return multiple rows: **WHERE d.DVD_ID = :NEW.DVD_ID** because my store_copy table has repeating dvd_id's. How does it pick the correct row (the one updated) when evaluating :NEW.DVD_ID? — COOLBEANS, Mar 12 '14 at 01:20
If you'll notice, that statement is reading the DVD table, where (I'm guessing) the DVD_ID is unique. This trigger is run once for each row updated in STORE_COPY, so the :NEW values all refer the the row which was updated, and thus there's only one :NEW.DVD_ID at a time. If you update 10 rows in STORE_COPY this trigger will be invoked 10 times, once for each STORE_COPY row that was updated. I hope this helps. — Bob Jarvis - Слава Україні, Mar 12 '14 at 16:46

PLSQL: syntax problems with trigger

3 Answers3