SQL - 触发器更新错误 [英] SQL - Trigger Update Error

问题描述

我开始学习SQL，并建立了一个房屋数据库，我有一个房屋，房间，走廊等的桌子。我在房子里有一个列，名为NumberOfRooms，房间里的一个列叫做HouseName。

我正在尝试触发一个房间，当房间被添加到房子时，房屋属性NumberOfRooms会增加。

这是我正在尝试的查询：

  CREATE TRIGGER UpdateNoRooms AFTER INSERT AS 
更新房屋SET Houses.NumberOfRooms = SELECT COUNT（Room）
 FROM Rooms 
 WHERE Rooms.HouseName = Houses.Name; 
  

，但它不工作。

这是我收到的错误消息：

错误1064（42000 ）：您的SQL语法有错误;检查与您的MySQL服务器版本对应的手册，以使用'at line 1

有什么想法可以解决这个问题？谢谢你提前

解决方案

请参阅 MySQL文档。您缺少命令的几个必需部分：

1. 您需要 ON tablename 告诉它触发器应该附加哪个表。




2. 您需要 FOR EACH ROW 触发器之前。




3. 您不需要计数房间。添加房间只需将房间数增加1。

所以应该是：

  CREATR TRIGGER UpdateNoRooms 
 AFTER INSERT ON客房
为每个R $ $ $ $ $ $ $ $ $ $ $％ NumberOfRooms = h.NumberOfRooms + 1 
 WHERE h.HouseName = NEW.HouseName 
  

I am beginning to learn SQL, and have made a database of Houses where I have a table for Houses, Rooms, Hallways, etc. I have a column in Houses called NumberOfRooms, and a column in Rooms called HouseName.

I am trying to make a trigger so that when a room is added to a house, the House attribute "NumberOfRooms" is incremented.

This is the query I am trying:

CREATE TRIGGER UpdateNoRooms AFTER INSERT AS 
UPDATE Houses SET Houses.NumberOfRooms = SELECT COUNT(Room) 
                                         FROM Rooms 
                                         WHERE Rooms.HouseName = Houses.Name;

, but it is not working.

This is the error message that I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS UPDATE Houses SET Houses.NumberOfRooms = SELECT COUNT(Room) FROM Rooms WHERE ' at line 1

Any ideas on how I can fix this? Thank you in advance

解决方案

See the syntax diagram in the MySQL documentation. You're missing several required parts of the command:

1. You need ON tablename to tell it which table the trigger should be attached to.

2. You need FOR EACH ROW before the trigger body.

3. You don't need to count the rooms. Adding a room simply increases the room count by 1.

So it should be:

CREATR TRIGGER UpdateNoRooms 
AFTER INSERT ON Rooms
FOR EACH ROW
    UPDATE Houses AS h
    SET h.NumberOfRooms = h.NumberOfRooms + 1
    WHERE h.HouseName = NEW.HouseName

这篇关于SQL - 触发器更新错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！