Question 22196 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Triggers: Inserting a variable

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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 ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search