我正在嘗試編寫觸發(fā)器，我有下表：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 | | +-----------+---------+------+-----+---------+----------------+狀態(tài)表：+------------+--------------------------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+--------------------------------------------------+------+-----+---------+-------+| idStatus | int(11) | NO | PRI | NULL | || nameStatus | enum('underConsideration','approved','rejected') | YES | | NULL | |+------------+--------------------------------------------------+------+-----+---------+-------+我需要一個(gè)觸發(fā)器，如果將具有相同ID的請(qǐng)求插入到OccupiedRoom表中，它將把BookingReques中的狀態(tài)更改為1，所以我嘗試了類似的操作create trigger occupy_trig after insert on OccupiedRoom for each rowbegin if BookingRequest.idRequest= NEW.idRequest then update BookingRequest set status = '1'; where idRequest = NEW.idRequest; end if;END;而且它不起作用，所以任何建議都非常有用
3 回答
Helenr
TA貢獻(xiàn)1780條經(jīng)驗(yàn) 獲得超4個(gè)贊
嘗試這個(gè)：
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 ;
嚕嚕噠
TA貢獻(xiàn)1784條經(jīng)驗(yàn) 獲得超7個(gè)贊
有了您的需求，您就不需要了BEGIN END，IF而不必要SELECT的觸發(fā)了。所以你可以簡(jiǎn)化為
CREATE TRIGGER occupy_trig AFTER INSERT ON occupiedroom
FOR EACH ROW
UPDATE BookingRequest
SET status = 1
WHERE idRequest = NEW.idRequest;
添加回答
舉報(bào)
0/150
提交
取消