IF Else condition in a Trigger Fails to Detect NULL Value for FirstConnectionDatetime Parameter in before Update Statement

Question

I have a after update MariaDB trigger set up to perform certain actions based on the value of FirstConnectionDatetime in the CustomerPurchases table. However, I noticed that the trigger condition IF NEW.FirstConnectionDatetime IS NOT NULL is incorrectly evaluating to true even when I do not provide the FirstConnectionDatetime parameter in the update statement (thus it should be NULL). This unexpected behavior is causing issues in my database operations.

Answer 1

when I do not provide the FirstConnectionDatetime parameter in the update statement (thus it should be NULL)

Let's imagine that there is no a column in the UPDATE query. In this case the value before UPDATE and the value after it is the same. And both OLD.column and NEW.column are equal to this value.

OLD.column contains the value before UPDATE, NEW.column accordingly contains the value after UPDATE executed successfully. And none of them stores the value provided (or NULL if not provided) in the query!

It seems that you confuse NEW.column value and the value which is returned by VALUES(column) function.