MySQL触发器遍历表

更多在这个贴子https://blog.youkuaiyun.com/qq_28430851/article/details/78043116

BEGIN
  DECLARE id int;  
  DECLARE f_d datetime;
  DECLARE t_d datetime;
  DECLARE st int;
  DECLARE done INT DEFAULT 0;
  DECLARE conflict INT DEFAULT 0;
  DECLARE result CURSOR FOR SELECT r_id, from_date, to_date, state FROM order_info ;  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
if date(new.from_date) >= date(new.to_date) then 
 SET done = 1;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "from_date cannot be later than to_date";
end if;
  OPEN result;  
  read_loop:LOOP  
    IF done   
    THEN LEAVE read_loop ;  
    END IF ; 
    FETCH result INTO id, f_d, t_d,st;    
    if id = new.r_id and st = 1 then
    if (DATE(new.from_date) BETWEEN DATE(f_d) AND DATE_SUB(t_d,INTERVAL 1 DAY)) OR
      (DATE_SUB(DATE(new.to_date),INTERVAL 1 DAY) BETWEEN DATE(f_d) AND DATE_SUB(t_d,INTERVAL 1 DAY)) OR
      (DATE(f_d) >= DATE(new.from_date) AND DATE(t_d) <= DATE(new.to_date)) then 
      set done = 1;
      set conflict = 1;
     end if;
    end if;
  END LOOP ;  
  CLOSE result;
  if conflict = 1 then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "The room has been booked in this time period";
  end if;
END