开发者社区> 问答> 正文

插入触发器后的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; 而且它不起作用,所以任何建议都非常有用

展开
收起
保持可爱mmm 2020-05-17 10:30:08 328 0
1 条回答
写回答
取消 提交回答
  • 尝试这个:

    DELIMITER $$ CREATE TRIGGER occupy_trig AFTER INSERT ON OccupiedRoom FOR 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
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
搭建电商项目架构连接MySQL 立即下载
搭建4层电商项目架构,实战连接MySQL 立即下载
PolarDB MySQL引擎重磅功能及产品能力盛大发布 立即下载

相关镜像