Trigger

Obliczanie ilości dni na podstawie daty początku i końca – przy wstawianiu nowego rekordu

DELIMITER $$
CREATE TRIGGER bi_days_total 
  BEFORE INSERT
  ON schedules
  FOR EACH ROW 
  BEGIN
    IF TO_DAYS(NEW.date_from) > 0 AND TO_DAYS(NEW.date_to) > 0 THEN
      SET NEW.days_total = TIMESTAMPDIFF(DAY, NEW.date_from, NEW.date_to) + 1;
    END IF;
  END $$
DELIMITER ;

Przy aktualizowaniu rekordu

DELIMITER $$
CREATE TRIGGER bu_days_total 
  BEFORE UPDATE
  ON schedules
  FOR EACH ROW 
  BEGIN
    IF TO_DAYS(NEW.date_from) > 0 AND TO_DAYS(NEW.date_to) > 0 THEN
      SET NEW.days_total = TIMESTAMPDIFF(DAY, NEW.date_from, NEW.date_to) + 1;
    END IF;
  END $$
DELIMITER ;

Usuwanie triggera

DROP TRIGGER bu_days_total;