Re: Need help creating mysql trigger
Peter, I get sql errors when I try to create a trigger using your suggested code:
mysql> CREATE TRIGGER location BEFORE INSERT ON download
-> FOR EACH ROW declare vcountry, varea, vcity varchar(50);
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 'declare vcountry, varea, vcity varchar(50)' at line 2
mysql> select country,area,city into vcountry,varea,vcity
-> from ip_lookup
-> where inet_aton(ADDRESS) between NEW.start_ip and NEW.end_ip
-> limit 1;
ERROR 1327 (42000): Undeclared variable: vcountry
mysql> if vcountry is not null then
-> set NEW.country=vcountry, NEW.area=varea, NEW.city=vcity;
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 'if vcountry is not null then
set NEW.country=vcountry, NEW.area=varea, NEW.cit' at line 1
mysql> else
-> set NEW.country='Unknown', NEW.area='Unknown', NEW.city='Unknown';
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 'else
set NEW.country='Unknown', NEW.area='Unknown', NEW.city='Unknown'' at line 1
mysql> end if;
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 'end if' at line 1
mysql>
mysql>
I'm totally lost