The following adds a country code to each new row inserted, in this case "US" for "United States".
CREATE TRIGGER `country` BEFORE INSERT ON `test`
FOR EACH ROW SET NEW.`countryCode` = CASE
WHEN NEW.`country` LIKE 'United States' THEN 'US'
END
The above works well, but I also want to insert a variable. The code below generates a #1064 syntax error:
SET @countryCode = 'US'
CREATE TRIGGER `country` BEFORE INSERT ON `test`
FOR EACH ROW SET NEW.`countryCode` = CASE
WHEN NEW.`country` LIKE 'United States' THEN @countryCode
END
Ultimately, I want to be able to use a variable so that multiple country codes can be concatenated and inserted in the same column.
2
Answers
This worked:
You need to define the variable inside the trigger.
Try: