CREATE TRIGGER check_appareillage_update BEFORE UPDATE ON hospitalisation FOR EACH ROW
  BEGIN
    DECLARE appareillage_chambre TINYINT;
    IF NEW.appareillage = 1 THEN
      SET appareillage_chambre = (SELECT appareillage FROM chambre c, lit l, occupe o
                                 WHERE c.numCh = l.chambre AND l.numL = o.numL AND o.nSS = NEW.nss);
      IF appareillage_chambre = 0 THEN
        signal sqlstate '45000' set message_text = 'La chambre n est pas appareillée';
      end if;
    end if;
  END;