MySQL Forums
Forum List  »  Triggers

Trigger and IF EXISTS
Posted by: Dirk Olbertz
Date: October 31, 2005 11:46PM

Hi there,

I wanted to try the Triggers in MySQL 5.1 and did the following:
CREATE TRIGGER overall_country_stats AFTER INSERT ON tbl_log_rows
FOR EACH ROW BEGIN
IF EXISTS (SELECT * FROM tbl_overall_country_stats WHERE country=NEW.country) THEN
UPDATE tbl_overall_country_stats
SET impressions = impressions + 1
WHERE country=NEW.country;
ELSE
INSERT INTO tbl_overall_country_stats (country, impressions) VALUES(NEW.country, 1);
END IF
END;

The idea is, that whenever a new row is added to the logfile (tbl_log_rows), the number of accesses for a specific country is updated in the table tbl_overall_country_stats. The thing is, that the country may yet not be in the table, so I need to check, if it's in there. And then either do an UPDATE oder an INSERT. But this does not work. I'm not quite sure if that problem is only Trigger related, but the error message does not give me much of an advice:
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 'UPDATE tbl_overall_country_stats
SET impressions = impressions + 1
' at line 4

Has anyone a hint, what's wrong with that statement?

Thanks,
Dirk

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger and IF EXISTS
25501
October 31, 2005 11:46PM
5704
November 02, 2005 08:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.