CREATE OR REPLACE TRIGGER SOLD
Can anyone see what's wrong here. Fiddling with it for ages.
BEFORE UPDATE OF STATUS ON STORE_COPY
FOR EACH ROW
DECLARE
RENT_OR_S NVARCHAR2(6);
UPDATED_DVD_ID NUMBER(10);
BEGIN
UPDATED_DVD_ID := (SELECT DVD_ID FROM STORE_COPY WHERE :NEW.STATUS != :OLD.STATUS);
RENT_OR_S := (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_S FROM DUAL);
IF :NEW.STATUS != :OLD.STATUS
THEN
IF :OLD.STATUS = 'Y'
THEN
IF :NEW.STATUS = 'N'
THEN
IF RENT_OR_S = '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 error message looks like this:
Error at line 6: PLS-00103: Encountered the symbol "STORE_COPY" when expecting one of the following:
) , group having intersect minus order start union where
connect
4. DECLARE
5. RENT_OR_S NVARCHAR2(6);
6. UPDATED_DVD_ID NUMBER(10);
7. BEGIN
8. UPDATED_DVD_ID := (SELECT DVD_ID FROM STORE_COPY WHERE :NEW.STATUS != :OLD.STATUS);