0

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.

  • Where do you get this error? What is on the line 95 of the script? – wolfrevokcats Sep 7 '17 at 19:25
2

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).

  • @alex07 .. Does it mean that I need to change from (:NEW.ticket_date > (screening_time + 15)) to (:NEW.ticket_date > (screening_time + (15 * 24 * 60))) ... I just need to confirm if this is what you meant. – learningIT Sep 8 '17 at 2:53
  • @learningIT : nope , screening_time+15/(24*60) – a1ex07 Sep 8 '17 at 15:54
  • Instead of screening_time+15/(24*60), I am thinking of using screening_time + INTERVAL '15' MINUTE . What do you think ? – learningIT Sep 8 '17 at 17:19
  • That would work as well. In fact, it's way more readable . – a1ex07 Sep 8 '17 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.