user3246938 user3246938 - 7 months ago 16
SQL Question

MySQL trigger will not execute will NULL value for one field

Hi I'm having issues with a trigger not running when I have missing field data.

When I create a new record I would like the filled fields to be concatenated and the empty fields to remain blank.

For example in my form it is not always required to have an email address however if I do not enter something in for the email address the trigger does not run and the data field I do need doesn't get concatenated. My current work round is to add a default value to the email field but I would rather it be blank and for the the trigger to run even when some fields are empty. In this case it is just the email address which will be blank from time to time.

BEGIN
SET NEW.data = CONCAT(NEW.green, ',', NEW.language, ',', NEW.map, ',', NEW.welcome, ',', NEW.label, ',', NEW.path, ',', NEW.email);
END


Any advise is gratefully received.

Answer

I'm still not sure I understand 100% of your question so here is my answer based on my understanding of your question:

BEGIN
  SET NEW.data = IF( NEW.email IS NULL OR NEW.email = '',
                    CONCAT(NEW.green, ',', NEW.language, ',', NEW.map, ',', NEW.welcome, ',', NEW.label, ',', NEW.path),
                    CONCAT(NEW.green, ',', NEW.language, ',', NEW.map, ',', NEW.welcome, ',', NEW.label, ',', NEW.path, ',', NEW.email)
                    );
END

Note that this is if email is empty more than 50% of the time. If the reverse is true, you should change the condition to NEW.email IS NOT NULL AND NEW.email != '' and reverse the CONCATs for optimal execution.