DELIMITER $$
CREATEPROCEDURE changeCon(IN _HID INT,IN flag INT)BEGINIF(flag =1)THENUPDATE House
SET con =1WHERE House.HID = _HID;ELSEUPDATE House
SET con =0WHERE House.HID = _HID;ENDIF;END$$
DELIMITER;
(2)查询对应收费记录对应的房屋编号的存储过程
DELIMITER $$
CREATEPROCEDURE findHID(IN _CID INT,OUT _HID INT)BEGINSELECT HID
INTO _HID
FROM Charge,Record
WHERE Charge.RID = Record.RID;END$$
DELIMITER;
2.触发器的代码
(1)租房房屋状态触发器(当租赁者租房成功后,更新所租房屋的状态为1,表示房屋已租用)
DELIMITER $$
CREATETRIGGER tri1 AFTERINSERTON Charge FOR EACH ROWBEGINCALL findHID(NEW.CID,@HID1);CALL changeCon(@HID1,1);END$$
DELIMITER;
(2)退房房屋状态触发器(当租赁者退房成功后,更新所退房屋的状态为0,表示房屋未租用)
DELIMITER $$
CREATETRIGGER tri2 BEFORE DELETEON Charge FOR EACH ROWBEGINCALL findHID(OLD.CID,@HID2);CALL changeCon(@HID2,0);END$$
DELIMITER;