There is a simple movie schedule table in the cinema:

CREATE TABLE seance ( start_time DATE NOT NULL, -- дата начала фильма (с точностью до минут) films_id_film NUMBER(30) NOT NULL, -- идшник фильма, который будет идти cinema_hall_id_hall NUMBER(30) NOT NULL -- идшник зала, в котором будет идти final_time DATE NOT NULL -- дата окончания фильма (с точностью до минут) ); ALTER TABLE seance ADD CONSTRAINT seance_pk PRIMARY KEY ( cinema_hall_id_hall, films_id_film, start_time, final_time );

Task : to write a trigger that does not allow specifying different films in intersecting periods of time and in the same room . That is, if a film with films_id_film = 1 is 01/01/1999 13:00 - 15:00 in the hall with cinema_hall_id_hall = 1, then you cannot insert the film into the table with the same ID of the hall, whose start date will be 01.01. 1999 2:00 p.m. Thanks in advance for your help! Attempts to solve: there are attempts to solve, but all this does not work. Here is an attempt:

 CREATE OR REPLACE TRIGGER check_seance AFTER INSERT OR UPDATE ON SEANCE FOR EACH ROW DECLARE realFinalDate DATE; BEGIN SELECT final_time INTO realFinalDate FROM SEANCE WHERE films_id_film = :new.films_id_film AND cinema_hall_id_hall = :new.cinema_hall_id_hall AND trunc(start_time) = trunc(:new.start_time) AND start_time <> :new.start_time; IF realfinaldate > :new.start_time THEN raise_application_error(-20000, 'Добавление фильма в тот же зал в то же время недопустимо!!!'); END IF; END; /
  • And with what actually problems? Add an example of your trigger and error code. - Viktorov
  • I can not write directly to the body of the trigger - Dmitry Yakimov
  • Just make a minimal and self-sufficient example for your task. For example, I do not have the desire to do the trigger for you. However, I am ready to suggest how to fix it. Or explain why he is not working now - Viktorov
  • And the trigger, then why? Enough ordinary check constraint. - Akina
  • The trigger for each row cannot work with its table. We need a common trigger (without for each), but it will be forced to control the intersection globally throughout the table. If the speed of work is not suitable, then you need 2 trigger. The first for each row, which will save changing records in another table. The second for the entire query (without for each row), which takes the saved id and checks these records for intersections, after which it deletes the id, so that the auxiliary table is always empty. - Mike

0