SQL - Trigger Update Error

Question

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

Answer 1

here is a quick version - you have denormalized - which is not a good idea. instead the number of rooms should be left to query time.

CREATE TRIGGER UpdateNoRooms AFTER INSERT AS
BEGIN
    UPDATE Houses h
        SET h.NumberOfRooms = h.NumberofRooms+1 WHERE :new.HouseName = h.Name;
END;

Answer 2

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