-------- Original Message -------- Subject: MySQL Conditional Trigger From: Dee Ayy <dee.ayy@xxxxxxxxx> To: php-db@xxxxxxxxxxxxx Date: 31.10.2008 17:09
I don't think my trigger likes a comparison with a variable which is NULL. The docs seem to have a few interesting variations on playing with NULL and I was hoping someone would just throw me a fish so I don't have to try every permutation (possibly using CASE, IFNULL, etc.). If my ShipDate (which is a date datatype which can be NULL) changes to a non-null value, I want my IF statement to evaluate to TRUE. IF NULL changes to aDate : TRUE IF aDate changes to aDifferentDate : TRUE IF anyDate changes to NULL : FALSE In my trigger I have: ... IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN ... Which only works when ShipDate was not NULL to begin with. I suppose it evaluates the following to FALSE IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN (not liking the "NULL != '2008-10-31'" part) Please give me the correct syntax. TIA
anything compared to NULL is always false NULL = NULL (NULL included) => false NULL != anything (NULL included) => false that's why IS NULL exists I would go this way: IF NVL(OLD.ShipDate, -1) != NVL(NEW.ShipDate, -1) THEN -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
