-------- Original Message --------
Subject: [PHP-DB] MySQL Conditional Trigger
From: Dee Ayy <[EMAIL PROTECTED]>
To: php-db@lists.php.net
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
