插入触发器后的mysql，该触发器更新另一个表的列?mysql
我正在尝试编写触发器，我有下表：BookingRequest：
+-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | idRequest | int(11) | NO | PRI | NULL | auto_increment | | roomClass | int(11) | NO | | NULL | | | inDate | date | NO | | NULL | | | outDate | date | NO | | NULL | | | numOfBeds | int(11) | NO | | NULL | | | status | int(11) | NO | MUL | NULL | | | idUser | int(11) | NO | MUL | NULL | | +-----------+---------+------+-----+---------+----------------+ 状态表：
+------------+--------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------------------------------------------+------+-----+---------+-------+ | idStatus | int(11) | NO | PRI | NULL | | | nameStatus | enum('underConsideration','approved','rejected') | YES | | NULL | | +------------+--------------------------------------------------+------+-----+---------+-------+ 被占领的房间：
+--------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+----------------+ | idOccupation | int(11) | NO | PRI | NULL | auto_increment | | idRoom | int(11) | NO | | NULL | | | idRequest | int(11) | NO | | NULL | | +--------------+---------+------+-----+---------+----------------+ 我需要一个触发器，如果将具有相同ID的请求插入到OccupiedRoom表中，它将把BookingReques中的状态更改为1，所以我尝试了类似的操作
create trigger occupy_trig after insert on OccupiedRoom for each row begin if BookingRequest.idRequest= NEW.idRequest then update BookingRequest set status = '1'; where idRequest = NEW.idRequest;
end if;
END; 而且它不起作用，所以任何建议都非常有用
写回答
-
尝试这个：
DELIMITER $$ CREATE TRIGGER occupy_trig AFTER INSERT ON
OccupiedRoomFOR EACH ROW begin DECLARE id_exists Boolean; -- Check BookingRequest table SELECT 1 INTO @id_exists FROM BookingRequest WHERE BookingRequest.idRequest= NEW.idRequest;IF @id_exists = 1 THEN UPDATE BookingRequest SET status = '1' WHERE idRequest = NEW.idRequest; END IF;END; $$ DELIMITER ;来源：stack overflow
2020-05-17 10:33:02赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
