MySQL Trigger – How to Set Variable with Dynamic Name in Trigger

MySQLstored-procedurestrigger

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this:

delimiter ;;

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    DECLARE columnName VARCHAR(20);
    SET columnName = 'my_column';
    SET NEW.{columnName} = 'my value'; -- pseudo-code
END;;


delimiter ;

Best Answer

Depending on what you are trying to achieve with this, you may be out of luck.

First of all, names cannot be parametrised in MySQL otherwise than using a prepared statement.

– So, can we use a prepared statement here?

No. Prepared statements are not allowed in triggers, according to the manual:

SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers.

– All right, prepared statements can be used in stored procedures. Can we create a stored procedure that would use a prepared statement to do the job, and call the procedure from the trigger?

Nope. A stored procedure cannot reference NEW and OLD columns – those namespaces are available only in triggers.

The only option appears to be an IF ... ELSEIF ... statement comparing columnName against a fixed set of names and assigning values to corresponding columns.

IF columnName = 'colname1' THEN
   SET NEW.colname1 = '...';
ELSEIF columnName = 'colname2' THEN
   SET NEW.colname2 = '...';
ELSEIF
   ...
END IF;

This method will not work if you are trying to include non-existent names (in order to cover columns that might be added later, for instance) in the checkings. And, of course, this method will just be unsuitable for you if you are trying to stipulate for unknown names.

But then, perhaps, what you are trying to do is not what you should be doing to begin with. Perhaps, you should revisit your schema and consider a different data model (at least for some portion of your data), like EAV, for instance.

Related Question