user3246938 user3246938 - 2 years ago 66
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.

SET = CONCAT(, ',', NEW.language, ',',, ',', NEW.welcome, ',', NEW.label, ',', NEW.path, ',',;

Any advise is gratefully received.

Answer Source

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

  SET = IF( IS NULL OR = '',
                    CONCAT(, ',', NEW.language, ',',, ',', NEW.welcome, ',', NEW.label, ',', NEW.path),
                    CONCAT(, ',', NEW.language, ',',, ',', NEW.welcome, ',', NEW.label, ',', NEW.path, ',',

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 IS NOT NULL AND != '' and reverse the CONCATs for optimal execution.

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