PL/SQL Trigger to check ticket date before inserting a row in Ticket table

Question

These are the tables and sequences I have created and I have inserted their values:

 CREATE TABLE Ticket_Type
  (
   t_type_id  NUMBER(1) PRIMARY KEY,
   CONSTRAINT check_t_type_id CHECK(t_type_id  > 0),
   t_type VARCHAR(20) NOT NULL,
   t_type_price NUMBER(4,2) NOT NULL,
   CONSTRAINT check_t_type_price CHECK(t_type_price > 0),
   t_type_start_date DATE NOT NULL,
   t_type_end_date DATE,
   CONSTRAINT check_t_type_end_date CHECK((t_type_end_date IS NULL) OR (t_type_end_date >= t_type_start_date)),
   CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)
  );


 CREATE TABLE Screening
  (
   screening_id NUMBER(6) PRIMARY KEY,
   CONSTRAINT check_screening_id CHECK(screening_id > 0),
   plan_id NUMBER(4) NOT NULL,
   theatre_id NUMBER(1) NOT NULL,
   screening_date DATE DEFAULT CURRENT_DATE NOT NULL,
   screening_start_hh24 NUMBER(2) NOT NULL,
   CONSTRAINT check_start_hh24 CHECK(screening_start_hh24 BETWEEN 9 AND 22),
   screening_start_mm60 NUMBER(2) NOT NULL,
   CONSTRAINT check_start_mm60 CHECK(screening_start_mm60 BETWEEN 0 AND 59),
   CONSTRAINT unique_theatre_id_screening_date_screening_start_hh24_screening_start_mm60 UNIQUE(theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
  );


  CREATE TABLE Ticket
  (
   ticket_id NUMBER(8) PRIMARY KEY,
   CONSTRAINT check_ticket_id CHECK(ticket_id > 0),
   t_type_id NUMBER(1) NOT NULL,
   CONSTRAINT fk_t_type_id FOREIGN KEY(t_type_id) REFERENCES Ticket_Type(t_type_id),
   screening_id NUMBER(6) NOT NULL,
   CONSTRAINT fk_screening_id FOREIGN KEY(screening_id) REFERENCES Screening(screening_id),
   seat_id NUMBER(5) NOT NULL,
   ticket_date DATE DEFAULT SYSDATE NOT NULL,
   CONSTRAINT unique_screening_id_seat_id UNIQUE(screening_id,seat_id)
  );

CREATE SEQUENCE ticket_type_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE NOCYCLE;

CREATE SEQUENCE screening_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE NOCYCLE;

CREATE SEQUENCE ticket_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE NOCYCLE;

INSERT INTO ticket_type(t_type_id,t_type,t_type_price,t_type_start_date,t_type_end_date) 
VALUES (ticket_type_seq.nextval,'Adult',13.95,to_date('1/06/2017', 'DD/MM/YYYY'),to_date('30/11/2017', 'DD/MM/YYYY'));
INSERT INTO ticket_type(t_type_id,t_type,t_type_price,t_type_start_date,t_type_end_date) 
VALUES (ticket_type_seq.nextval, 'Concession',7,to_date('1/06/2017', 'DD/MM/YYYY'),to_date('30/11/2017', 'DD/MM/YYYY'));
INSERT INTO Ticket_Type (t_type_id,t_type,t_type_price,t_type_start_date)
VALUES (ticket_type_seq.NEXTVAL,'Student',7,TO_DATE('1/6/2017','DD/MM/YYYY'));

INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),11,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),13,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,2,TO_DATE('11/9/2017','DD/MM/YYYY'),13,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),17,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),12,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,2,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,2,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),12,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,1,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,2,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,3,TO_DATE('11/9/2017','DD/MM/YYYY'),19,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,4,TO_DATE('11/9/2017','DD/MM/YYYY'),21,0);

Now this is the question from the assignment:

Create a trigger called TR_valid_15_min_ticket which should check ticket_date before inserting a row in Ticket table. The ticket issued date (ticket_date) can not be greater than the current date and time. In addition, it cannot be greater than screening time + 15 minutes. Otherwise, for each case, the trigger should raise a proper error. The error messages must be meaningful.

Hint: screening time = screening_date + (screening_start_hh24)/24 + (screening_start_mm60)/(24*60)

Note: ticket_date is in Ticket table and screening_date,screening_start_hh24,screening_start_mm60 are in Screening table

This is what I have tried:

CREATE OR REPLACE TRIGGER TR_valid_15_min_ticket 
BEFORE INSERT
ON Ticket
FOR
EACH ROW
DECLARE
screening_time NUMBER;
v_screening_date Screening.screening_date%TYPE;
v_screening_start_hh24 Screening.screening_start_hh24%TYPE;
v_screening_start_mm60 Screening.screening_start_mm60%TYPE;
BEGIN
screening_time := v_screening_date + (v_screening_start_hh24)/24 + (v_screening_start_mm60)/(24*60);
IF (:NEW.ticket_date > SYSDATE) OR  (:NEW.ticket_date > (screening_time + 15))
THEN
RAISE_APPLICATION_ERROR(-20000,'Ticket date cannot be more than the current date nor can it have extra 15 minutes with addition to its screening time');
END IF;
END;

However, I am getting the following error:

Error(95,1): PL/SQL: Statement ignored
Error(95,19): PLS-00382: expression is of wrong type
Error(96,1): PL/SQL: Statement ignored
Error(96,55): PLS-00306: wrong number or types of arguments in call to '>'

It would be really helpful if a correct solution trigger code is provided.

Answer 1

screening_time variable is declared as number, but compared with date column (... (:NEW.ticket_date > (screening_time + 15))) . After changing screening_time type to DATE, you also need to change screening_time + 15 (the way it's written now, it means 15 days past screening_time, not 15 minutes).