mysql 在插入触发器后更新另一个表的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16892070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql after insert trigger which updates another table's column
提问by DeadKennedy
i'm trying to write a trigger, I have following tables: BookingRequest:
我正在尝试编写一个触发器，我有以下表格：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 | |
+-----------+---------+------+-----+---------+----------------+
status table:
状态表：
+------------+--------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------------------------------+------+-----+---------+-------+
| idStatus | int(11) | NO | PRI | NULL | |
| nameStatus | enum('underConsideration','approved','rejected') | YES | | NULL | |
+------------+--------------------------------------------------+------+-----+---------+-------+
OccupiedRoom:
占用房间：
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| idOccupation | int(11) | NO | PRI | NULL | auto_increment |
| idRoom | int(11) | NO | | NULL | |
| idRequest | int(11) | NO | | NULL | |
+--------------+---------+------+-----+---------+----------------+
i need a trigger which will change status in BookingReques to 1 if request with the same id is inserted into OccupiedRoom table, so i tried something like this
如果将具有相同 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;
and it doesn't work, so any suggestions would be very appriciated
它不起作用，所以任何建议都会非常有用
回答by user4035
Try this:
尝试这个：
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 ;
回答by peterm
With your requirements you don't need BEGIN ENDand IFwith unnecessary SELECTin your trigger. So you can simplify it to this
随着你的要求，你不需要BEGIN END和IF不必要的SELECT在你的扳机。所以你可以把它简化成这样
CREATE TRIGGER occupy_trig AFTER INSERT ON occupiedroom
FOR EACH ROW
UPDATE BookingRequest
SET status = 1
WHERE idRequest = NEW.idRequest;
回答by mhafellner
Maybe remove the semi-colon after set because now the where statement doesn't belong to the update statement. Also the idRequestcould be a problem, better write BookingRequest.idRequest
也许在设置后删除分号，因为现在 where 语句不属于更新语句。也idRequest可能是一个问题，最好写BookingRequest.idRequest
回答by Jaymin
DELIMITER //
CREATE TRIGGER contacts_after_insert
AFTER INSERT
ON contacts FOR EACH ROW
BEGIN
DECLARE vUser varchar(50);
-- Find username of person performing the INSERT into table
SELECT USER() INTO vUser;
-- Insert record into audit table
INSERT INTO contacts_audit
( contact_id,
deleted_date,
deleted_by)
VALUES
( NEW.contact_id,
SYSDATE(),
vUser );
END; //
DELIMITER ;
