Re: Need help creating mysql trigger

[Larry Hale]

Peter, I get an undeclared variable error using your latest code. I tried the following but it just sits there and does nothing in mysql shell:

mysql> DELIMITER //
mysql> CREATE TRIGGER access_ins BEFORE INSERT ON access
-> FOR EACH ROW begin
-> declare vcountry varchar(50);
-> declare varea varchar(50);
-> declare vcity varchar(50);
->
-> select country,area,city into vcountry,varea,vcity
-> from ip_lookup
-> where NEW.ADDRESS between ip_lookup.start_ip and ip_lookup.end_ip
-> limit 1;
-> if vcountry is not null then
-> set NEW.country=vcountry, NEW.area=varea, NEW.city=vcity;
-> else
-> set NEW.country='Unknown', NEW.area='Unknown', NEW.city=nknown';
'> end if;
'> END; //
'>
'> DELIMITER ;
'>


No errors are generated but nothing is happening.