Trigger na bazie danych

Cześć,
Potrzebuje dodać do bazy danych MySql zabezpieczenie przed wstawieniem niepożądanych rekordów.
Mam taka tabelkę:

CREATE TABLE `TEST` (
  `id` int NOT NULL,
  `building` int NOT NULL,
  `room` int NOT NULL,
  `start` datetime NOT NULL,
  `stop` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `TEST` (`id`, `building`, `room`, `start`, `stop`) VALUES
(1, 1, 1, '2022-12-06 08:00:00', '2022-12-06 09:00:00'),
(2, 1, 1, '2022-12-06 10:00:00', '2022-12-06 11:00:00'),
(3, 1, 1, '2022-12-06 11:00:00', '2022-12-06 13:00:00'),
(4, 1, 2, '2022-12-06 10:00:00', '2022-12-06 15:00:00'),
(5, 1, 3, '2022-12-06 09:00:00', '2022-12-06 13:00:00');

Prosta tabelka budynków z salami oraz godzinami w których dane pomieszczenie jest wynajęte.
Chciałbym ustawić jakieś zabezpieczenie aby nie można było dodać rekordu na dany budynek oraz sale jeśli jest ona w tym czasie zajęta, czyli np:

INSERT INTO `TEST` (`id`, `building`, `room`, `start`, `stop`) VALUES(6, 1, 2, '2022-12-06 08:00:00', '2022-12-06 11:00:00')

Kombinowałem z Triggerami ale nie udało mi się tego finalnie zrobić.
Zna ktoś może jakiś sposób i mógłby mnie naporowadzić?

Pokaż proszę trigger!
Bo pokazałeś wszystko co nie jest istotne dla problemu.

Aby dodać zabezpieczenie przed wstawieniem niepożądanych rekordów do bazy danych MySQL, możesz utworzyć w bazie danych indeks unikalny dla kolumn building, room i start, który zapobiegnie wstawieniu rekordu z takimi samymi wartościami tych kolumn. Dzięki temu uniemożliwisz dodanie rekordu z danym budynkiem i salą, jeśli już istnieje rekord z takim samym budynkiem, salą i datą rozpoczęcia.

Aby utworzyć indeks unikalny, należy wykonać następujące kroki:

ALTER TABLE `TEST`
ADD UNIQUE INDEX `unique_building_room_start` (`building`, `room`, `start`);

@chomikowski: indeksy nie ograją mi sytuacji gdy dla powyższego przykładu chciałbym dodać rekord który nachodzi godzinami na już isnitjący
czyli takie zapytanie by przeszło

INSERT INTO `TEST` (`id`, `building`, `room`, `start`, `stop`) VALUES(6, 1, 2, '2022-12-06 08:00:00', '2022-12-06 11:00:00')

co oznaczało by, że sala 2 jest wynajęta przez 2 osoby w godzinach 10:00 do 11:00

@Adamek Adam: gdybym miał jakiś sensowny to bym go tu wkleił, jedyne cu udało mi się stworzyć ale niestety nie działa wygląda tak:

BEGIN
        IF count(NEW.building) > 0 not in (
            select A.building
            From TEST A  
            where (NEW.building = A.building and NEW.room = A.room and NEW.start > A.start and NEW.stop < A.stop)
        ) THEN
           CALL `Insert Not Allowed.`;
        END IF;
    END

@piotr_c88 Pierwszy wynik z google
https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/31613668

Ja tam ze składnią mysql w triggerach nie jestem za bardzo obeznanyny ale na logikę:

count(NEW.building) > 0 not in (
            select A.building
            From TEST A  
            where (NEW.building = A.building and NEW.room = A.room and NEW.start > A.start and NEW.stop < A.stop)

Ten warunek jest zły.
Sprawdzasz czy daty dodawane nie są w zadanym okresie, czyli np, dopuszczasz sytuacje, że dodasz taki rekord

(9, 1, 2, '2022-12-06 10:00:00', '2022-12-06 15:00:00')

Czyli powielisz rezerwacje czy tam ją zdublujesz, musisz to dopracować

Udało się stworzyć taki trigger:

CREATE TRIGGER `test_insert` BEFORE INSERT ON `TEST` FOR EACH ROW BEGIN IF(
  select 
    COUNT(*) 
  From 
    TEST A 
  where 
    (
      NEW.building = A.building 
      and NEW.room = A.room 
      and (
        (
          (
            NEW.start BETWEEN A.start 
            AND DATE_ADD(A.stop, INTERVAL -1 SECOND)
          ) 
          OR (
            NEW.stop BETWEEN DATE_ADD(A.start, INTERVAL 1 SECOND) 
            AND A.stop
          )
        ) 
        OR (
          NEW.start < A.start 
          and NEW.stop > A.start
        )
      )
    )
) > 0 THEN CALL `Insert Not Allowed.`;
END IF;
END

oraz

CREATE TRIGGER `test_update` BEFORE 
UPDATE 
  ON `TEST` FOR EACH ROW BEGIN IF(
    select 
      COUNT(*) 
    From 
      TEST A 
    where 
      (
        NEW.building = A.building 
        and NEW.room = A.room 
        and (
          (
            (
              NEW.start BETWEEN A.start 
              AND DATE_ADD(A.stop, INTERVAL -1 SECOND)
            ) 
            OR (
              NEW.stop BETWEEN DATE_ADD(A.start, INTERVAL 1 SECOND) 
              AND A.stop
            )
          ) 
          OR (
            NEW.start < A.start 
            and NEW.stop > A.start
          )
        )
      )
  ) > 0 THEN CALL `Update Not Allowed.`;
END IF;
END

myślicie, że takie rozwiązanie będzie ok?

Jak pisałem na MySQL nie znam się za bardzo, ale przerwanie insertu robi się tak:

SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = "insert not allowed";

Nie bardzo wiem po co wywolujesz procedure

@Panczo: Ja też nie znam MySql-a za dobrze, a to jest mój pierwszy trigger

Nie bardzo wiem po co wywolujesz procedure

jest lesze rozwiązanie?

a masz procedurę: Update Not Allowed. w bazie.

Bo wydaje mi się, że jak nie masz to dostaniesz błąd nawet jeśli rekordy będą poprawne i nie dodasz nic do tabeli.

Ten trigger Ci działa?

Panczo napisał(a):

a masz procedurę: Update Not Allowed. w bazie.

Bo wydaje mi się, że jak nie masz to dostaniesz błąd nawet jeśli rekordy będą poprawne i nie dodasz nic do tabeli.

Ten trigger Ci działa?

Procedury nie mam, nic poza tymi dwoma triggerami nie dodawałem. Zarówno insert jak i update działa

To ja nie rozumiem tego zapisu, ale nie dodaje błędnych?

Panczo napisał(a):

To ja nie rozumiem tego zapisu, ale nie dodaje błędnych?

Tzn teraz sprawdzam dokładniej i insert przechodzi chociaż rzuca błędem: #1305 - PROCEDURE xxx.Insert Not Allowed. does not exist
ale dodaje nowe które się mieszczą i blokuje nie chciane
a Update faktycznie się sypie :/

IMO powinieneś użyć signal, aby błąd był czytelny jak ktoś kto nie pisał tego triggera nie zastanawiał się jakiej procedury brakuje. Co znaczy, że UPDATE się sypie?

Panczo napisał(a):

IMO powinieneś użyć signal, aby błąd był czytelny jak ktoś kto nie pisał tego triggera nie zastanawiał się jakiej procedury brakuje. Co znaczy, że UPDATE się sypie?

Dokładnie.
Baza rzuca wyjątkiem po polsku (jeśli w PL) np "Nie może być niedziela" (autentyczne), ponieważ jakiś rodzaj operacji tak został zaprojektowany. Nie jakiś "insert error $9876543"

Dla mnie lepszą warstwą jest kod warstwy repo/serwisu w języku programowania, ale jak wymagana kompatybilności z bardzo starymi / bardzo antywarstwowymi środowskami (PHP, Delphi) z kodu SQL jest akceptowalne