自动化mysql表中的数据计算

Question

I have 2 tables Environment and room:

Environment
+-------+------------+--------------+----------------------+
| Envid | Agregate   | Availability | RoomNo               |
+-------+------------+--------------+----------------------+

Room
+--------+----------+-------------+------------------+
| RoomNo | Humidity | Temperature | Lighting         |
+--------+----------+-------------+------------------+
|  1     |    50    |  40         |  9               |
|  2     |    79    |  25         |  7               |
|  3     |    50    |  40         |  9               |
+--------+----------+-------------+------------------+

Basically, Environment is an empty table. What I need is to automate its calculation:

1. Take the values of Humidity, temperature and lighting from Room on EACH ROW,
2. sum them up together. This I have been able to do-

SELECT RoomNo,Humidity,Temperature,Lighting, Humidity+Temperature+Lighting AS Agregate FROM Room;

I just need to:

1. Store this new calculated "Agregate" in Environment table as "Agregate"

2. Populate it with the corresponding room number of the table Room (via foreign key).

EDIT: 5. Availability(boolean) of Room, should Update to 1 when Agregate > 100.

Can this be done directly in mysql? or in PHP ?

Note: RoomNo in Environment is a Foreign key OF Primary key in Room

</div>

Answer 1

Use the following triggers to populate the Environment table.

delimiter |
CREATE TRIGGER `after_update_Room` 
  AFTER UPDATE ON `Room` FOR EACH ROW 
    BEGIN 
      UPDATE Environment
      SET Agregate = NEW.Humidity + NEW.Temperature + NEW.Lighting
      WHERE RoomNo = NEW.RoomNo;
    END;
|

delimiter |
CREATE TRIGGER `after_insert_Room`
    AFTER INSERT ON `Room` FOR EACH ROW
    BEGIN
       INSERT INTO Environment (`Agregate`, `RoomNo`) 
       SELECT Humidity+Temperature+Lighting AS Agregate, RoomNo 
       FROM Room 
       WHERE RoomNo = NEW.RoomNo;
    END;
|