Trigger That updates the status of a video if it gets rented out or returned

Question

I am creating a Trigger That updates the status of a video if it gets rented out or returned. At the bottom is my trigger, but it has errors. The following errors are as follows.

LINE/COL ERROR
6/28 PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( - + case mod new not null avg coun t current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe < an alternatively-quoted string literal with c haracter set specification>

6/51 PLS-00103: Encountered the symbol ";" when expecting one of the f ollowing: * & - + / at mod remainder rem then and or || multiset

--Video TABLE
 CREATE TABLE Video(
 Vid_Num VARCHAR2(10), 
 Category VARCHAR2(30),
 Status VARCHAR2(15),
 Title VARCHAR2(30),
 Catalog_Num VARCHAR2(10),
 Rental_fee NUMBER(8,2),
 Cost NUMBER(8,2),
 Main_Actors VARCHAR2(100),
 Director VARCHAR2 (50),
 Rental_Code VARCHAR2(10) CONSTRAINT Rental_ID_FK REFERENCES Rental(Rental_Code),
Num_Copies NUMBER(4),
 CONSTRAINT Vid_Num_PK PRIMARY KEY (Vid_Num, Catalog_Num));

--Insert for Video Table
INSERT INTO Video
    VALUES ('V101','Action','Available','Expendables','C1','4.99','0','Stallone','Simon West','R101','2');
INSERT INTO Video
    VALUES ('V102','Drama','Available','The Judge','C2','4.99','0','Robert Downey Jr','David Dobkin','R101','2');
INSERT INTO Video
    VALUES ('V103','Family','Available','Lion King','C3','1.99','0','James Jones','Rogers Allers','R105','3');
INSERT INTO Video
    VALUES ('V104','Suspense','Available','Saw II','C4','2.99','0','Tobin Bell','James Wan','R103','2');
INSERT INTO Video
    VALUES ('V105','Sci-Fi','Available','Interstellar','C5','4.99','0','Matthew McConaughy','Christopher Nolon','R101','2');
INSERT INTO Video
    VALUES ('V106','Action','Available','Hunger Games','C6','4.99','0','Jennifer Lawrence','Gary Ross','R101','2');
INSERT INTO Video
    VALUES ('V107','Action','Available','I Am Legend','C7','1.99','0','Will Smith','Francis Lawerence','R105','2');
INSERT INTO Video
    VALUES ('V108','Drama','Available','Hancock','C8','1.99','0','Will Smith','Peter Berg','R105','2');
INSERT INTO Video
    VALUES ('V109','Comedy','Available','Billy Madison','C9','1.99','0','Adam Sandler','Tamara Davis','R105','2');
INSERT INTO Video
    VALUES ('V110','Comedy','Available','Tommy Boy','C10','1.99','0','Chris Farley','Peter Segal','R105','2');

   --Transaction Table
    CREATE TABLE Transaction(
    Rental_Num VARCHAR2(8) CONSTRAINT Rental_Num_PK PRIMARY KEY,
    Mem_Num VARCHAR2(10) CONSTRAINT Member_Num_FK REFERENCES Member(Mem_Num),
    Full_Name VARCHAR2(50),
    Vid_Num VARCHAR2(10),
    Title VARCHAR2(50),
    Catalog_Num VARCHAR2(10),
    Date_Rented_Out DATE,
    Date_Returned DATE,
    Rental_Code VARCHAR2(10) CONSTRAINT Rental_Code_FK REFERENCES Rental(Rental_Code),
    Rental_fee NUMBER(8,2),
    CONSTRAINT Video_Num_FK FOREIGN KEY(Vid_Num, Catalog_Num) REFERENCES Video(Vid_Num, Catalog_Num));


    --Insert for Transaction Table
    INSERT INTO Transaction
        VALUES ('1','Mem102','OJ Simpson', 'V101', 'Expendables', 'C1', '14-Nov-14', '16-Nov-14', 'R101', '4.99');
    INSERT INTO Transaction
        VALUES ('2','Mem105','Donte Stallworth', 'V103', 'Lion King', 'C3', '14-Nov-14', '16-Nov-14', 'R105', '1.99');
    INSERT INTO Transaction
        VALUES ('3','Mem103','Ray Rice', 'V102', 'The Judge', 'C2', '14-Nov-14', '16-Nov-14', 'R101', '4.99');
    INSERT INTO Transaction
        VALUES ('4','Mem101','Mike Vick', 'V105', 'Intersteller', 'C5', '14-Nov-14', '16-Nov-14', 'R101', '4.99');
    INSERT INTO Transaction
        VALUES ('5','Mem104','Pacman Jones', 'V104', 'Saw II', 'C4', '14-Nov-14', '16-Nov-14', 'R103', '2.99');
    INSERT INTO Transaction
        VALUES ('6','Mem104','Pacman Jones', 'V106', 'Hunger Games', 'C6', '14-Nov-14', '16-Nov-14', 'R101', '4.99');
    INSERT INTO Transaction
        VALUES ('7','Mem104','Pacman Jones', 'V107', 'I Am Legend', 'C7', '14-Nov-14', '16-Nov-14', 'R105', '1.99');
    INSERT INTO Transaction
        VALUES ('8','Mem104','Pacman Jones', 'V108', 'Hancock', 'C8', '14-Nov-14', '16-Nov-14', 'R105', '1.99');
    INSERT INTO Transaction
        VALUES ('9','Mem104','Pacman Jones', 'V109', 'Billy Madison', 'C9', '14-Nov-14', '16-Nov-14', 'R105', '1.99');
    INSERT INTO Transaction
        VALUES ('10','Mem104','Pacman Jones', 'V110', 'Tommy Boy', 'C10', '14-Nov-14', '16-Nov-14', 'R105', '1.99');




--Trigger



     CREATE or REPLACE Trigger Available_Rule
BEFORE INSERT ON Transaction
     FOR EACH ROW

    BEGIN

    SELECT Date_Rented_Out,Date_Returned, Status
     FROM Transaction, Video;

    IF Date_Rented_Out > 0 AND IF Date_Returned = NULL;
     THEN Status = Unavailable;
     End IF;
     ELSE

    IF Date_Rented_Out > 0 AND IF Date_Rented > 0;
     THEN Status = Available;
     End IF;
     END;
     / 
     show errors;

OldProgrammer OldProgrammer · Accepted Answer · 2014-12-01T18:32:05

The context of the current trigger's row values are available with the default, :new property. You do not perform a select on the table you are triggered on. Instead try this (you may have to tweak a bit, but basic concept). Note that DATE data types are not checked against zero. It should either have a NULL value, or some other valid value. You will need to fix that. I would suggest spending some time reading about PL/SQL syntax also.

 CREATE or REPLACE Trigger Available_Rule
 BEFORE INSERT ON Transaction
 FOR EACH ROW
BEGIN


IF :new.Date_Rented_Out  is not null AND IF :new.Date_Returned = NULL
 THEN 
   update video
   set status = 'Available'
   where vid_num = :new.vid_num;
 End IF;
 ELSE

  -- etc. checks ---
 END;
 /

Trigger That updates the status of a video if it gets rented out or returned

1 Answers