MySQL Triggers: Inserting a variable-CodePudding

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.

CodePudding user response：

You need to define the variable inside the trigger. Try:

delimiter //
CREATE TRIGGER `country` BEFORE INSERT ON `test`
FOR EACH ROW 
BEGIN
 SET @countryCode = 'US';
 SET NEW.`countryCode` = 
        CASE WHEN NEW.`country` LIKE 'United States' 
           THEN @countryCode end;
END; //
delimiter ;

CodePudding user response：

This worked:

CREATE TRIGGER `country` BEFORE INSERT ON `test`
FOR EACH ROW IF (NEW.tagline LIKE '%United States%') THEN
SET @countryCode = "US";
SET NEW.`taglineCode` = @countryCode;
END IF