MySQL IF logic inside trigger with NEW and OLD

Question

Apologies for the vague title - hopefully this explains better:

The below trigger is firing after an update (as designed) but the two IF statements are firing when neither the prospectid or propertyid fields are updated.

So the first question is, is there something wrong with the IF logic? I do recall having some issues with <> vs <=>...?

CREATE TRIGGER myTrig AFTER UPDATE ON t_enquiries
FOR EACH ROW
BEGIN
IF (NEW.prospectid <=> OLD.prospectid) OR (NEW.propertyid <=> OLD.propertyid) THEN
    IF (NEW.prospectid IS NOT NULL) AND (NEW.propertyid IS NOT NULL) THEN
        INSERT INTO t_prospect_property_link (prospectid, propertyid, remaininginspections) VALUES (NEW.prospectid, NEW.propertyid, 10)
        ON DUPLICATE KEY UPDATE subscribed = 1, remaininginspections = 10;
    END IF;
END IF;
END

If my IF statements are ok, surely this statement which fires the trigger isn't causing the prospectid and propertyid fields to do something to make the IF evaluate true???

UPDATE t_enquiries SET recordstatus = 3, enquirystatus = 1 WHERE id = xxx;

Answer 1

<> is a "not equal to" comparison operator. a <> b will evaluate to TRUE when a and b are both non-NULL and are not equal to each other.

<=> (spaceship) operator is the null-safe equality comparison operator.

a <=> b is shorthand for ( a = b OR ( a IS NULL AND b IS NULL ))

Once we have a good handle on SQL tri-valued boolean logic (TRUE, FALSE, NULL), the operators should not present any difficulty.

One area that can be confusing is the order of precedence of the operators. But fortunately, that is documented in the MySQL Reference Manual here: https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html

We can use parens to override the order of precedence, which we frequently need to do when both OR and AND operators are involved.

---

It's not clear what you are trying to achieve, so we're just guessing.

Maybe you are wanting to check that value of a column has changed.

IF NOT NEW.col <=> OLD.col THEN
   -- value of col has changed
END IF;