This adds a country code on the same row before a particular country is inserted:
CREATE TRIGGER `trig` BEFORE INSERT ON `table`
FOR EACH ROW SET NEW.`code` = CASE NEW.`country`
WHEN 'United States' THEN 'USA'
WHEN 'United Kingdom' THEN 'UK'
WHEN 'United Arab Emirates' THEN 'UAE'
END
I am trying to create something similar for cities, but I need the trigger to fire whenever the new city contains a particular term like so:
"Brooklyn, New York" --> NYC
"Greater London Area" --> LON
In psuedo-code this would be:
WHEN CONTAINS 'NEW YORK' THEN 'NYC'
Please note that this is not a SELECT statement. This is a trigger which seems to have different syntax. None of the following variations (implied by the answers to the other questions work. They all generate errors.
WHEN NEW.`country`.contains('NEW') THEN 'NYC'
WHEN contains('NEW') THEN 'NYC'
WHEN NEW.`country` LIKE '%New%' THEN NEW.`code` = 'NYC'
WHEN NEW.`country` LIKE '%New%' THEN 'NYC'
