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;

DELIMITER //

CREATE TRIGGER product_before_update
BEFORE UPDATE
ON products FOR EACH ROW

BEGIN
DECLARE tempVar varchar(1000);

SET tempVar := '[Product update] ' + old.name;

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

DELIMITER ;


So basically
tempVar
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.

EDIT2:

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