Re: Error creating Trigger

[Jassim Rahma]

Thanks Peter..

I have another problem please..


22:10:13 CREATE TRIGGER trigger_monitor AFTER INSERT ON monitor FOR EACH ROW BEGIN UPDATE hosts SET average_time = (SELECT AVG(roundtrip_time) FROM monitor WHERE host_id = NEW.host_id), maximum_time = (SELECT MAX(roundtrip_time) FROM monitor WHERE host_id = NEW.host_id), minimum_time = (SELECT MIN(roundtrip_time) FROM monitor WHERE host_id = NEW.host_id) WHERE host_id = NEW.host_id Error Code: 1064. 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 '' at line 9 0.016 sec




Here is the updated trigger:

USE maxmon;

CREATE TRIGGER trigger_monitor
AFTER INSERT
ON monitor FOR EACH ROW
BEGIN
UPDATE hosts
SET average_time = (SELECT AVG(roundtrip_time) FROM monitor WHERE host_id = NEW.host_id),
maximum_time = (SELECT MAX(roundtrip_time) FROM monitor WHERE host_id = NEW.host_id),
minimum_time = (SELECT MIN(roundtrip_time) FROM monitor WHERE host_id = NEW.host_id)
WHERE host_id = NEW.host_id;

END;