Linesofcode Linesofcode -4 years ago 136
MySQL Question

MySQL Trigger concatenate multiple string into a variable

I need to concatenate string into a trigger variable, because of the conditions;


CREATE TRIGGER product_before_update
ON products FOR EACH ROW

DECLARE tempVar varchar(1000);

SET tempVar := '[Product update] ' +;

IF(OLD.quantity != NEW.quantity) THEN
tempVar += 'Quantity changed: ' . OLD.quantity . ' to ' . NEW.quantity;
END; //


So basically
will have multiple information and I'd like to write everything on a variable so later I could add to a table of logs.

The error is on the
tempVar +=
line because it doesn't understand the + signal.

Answer Source

You need a sequence of CONCAT:

tempVar := CONCAT(CONCAT(CONCAT(CONCAT(tempVar, 'Quantity changed: '), OLD.quantity), ' to '), NEW.quantity);

If quantity is numeric, then you need to cast it to varchar, like this:

tempVar := CONCAT(CONCAT(CONCAT(CONCAT(tempVar, 'Quantity changed: '), CAST(OLD.quantity as varchar(50))), ' to '), CAST(NEW.quantity as varchar(50)))

EDIT: Paul Spiegel's constructive criticism is valid, so I add its idea into the answer

Without CAST:

tempVar := CONCAT(tempVar, 'Quantity changed: ', OLD.quantity, ' to ', NEW.quantity);

With CAST:

tempVar := CONCAT(tempVar, 'Quantity changed: ', CAST(OLD.quantity as varchar(50)), ' to ', CAST(NEW.quantity as varchar(50)));

In this answer I assumed that OLD.quantity and NEW.quantity are not null. If they can be null, then you need to check whether it is null and handle that case as well.


We need to use the SET keyword before tempVar each time we assign a value to it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download