MySQL Conditional Trigger

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

On Fri, Oct 31, 2008 at 11:09 AM, Dee Ayy wrote:
> ...
> IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN
> ...

The following seems to work. Is it correct and not too verbose?
IF (OLD.StatusShipDate IS NULL AND NEW.StatusShipDate IS NOT NULL) OR
(OLD.StatusShipDate != NEW.StatusShipDate AND NEW.StatusShipDate IS
NOT NULL) THEN

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

-------- Original Message --------
Subject: [PHP-DB] MySQL Conditional Trigger
From: Dee Ayy
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

RE: MySQL Conditional Trigger

Dee,

If all you're trying to code is that a value of NULL equates to FALSE
and a date value (whatever date value) equates to true, you can use
something like this:

If ($MyVariable) {
//... true path blah...
} else {
//... false path blah...
}

You can use this because NULL equates to false. If you prefer something
more concise, try the following:

$MyVariable ? //True : //False

I'm not a PHP guy so take this with a grain of salt. If I'm full of it,
don't hesitate to correct me.

A-

-----Original Message-----
From: OKi98 [mailto:oki98@centrum.cz]=20
Sent: Monday, December 08, 2008 4:33 PM
To: Dee Ayy
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] MySQL Conditional Trigger

-------- Original Message --------
Subject: [PHP-DB] MySQL Conditional Trigger
From: Dee Ayy
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 !=3D 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 !=3D '2008-10-31' AND '2008-10-31' IS NOT NULL THEN
> (not liking the "NULL !=3D '2008-10-31'" part)
>
> Please give me the correct syntax.
> TIA
>
> =20
anything compared to NULL is always false
NULL =3D NULL (NULL included) =3D> false
NULL !=3D anything (NULL included) =3D> false
that's why IS NULL exists

I would go this way:

IF NVL(OLD.ShipDate, -1) !=3D NVL(NEW.ShipDate, -1) THEN




--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

> anything compared to NULL is always false

Actually it's null.

mysql> select false = null;
+--------------+
| false = null |
+--------------+
| NULL |
+--------------+
1 row in set (0.01 sec)

mysql> select 1 = null;
+----------+
| 1 = null |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)

mysql> select 2 = null;
+----------+
| 2 = null |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)


unknown compared to anything is unknown.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: MySQL Conditional Trigger

All,

Chris makes a good point i.e., "unknown compared to anything is
unknown". His comment made me want to clarify my earlier note. In my
earlier example, I wasn't suggesting that-that logic be coded in the
database. I was assuming it would be in the page:


$TestNullValue =3D NULL;

If ($TestNullValue) {
print "Evaluates to true!";
} else {
print "Evaluates to false!";
}

?>

I tested this, and it worked. Here is why, PHP (like pretty much every
other language) silently casts a NULL to false:=20

"When converting to boolean, the following values are considered FALSE:

- the boolean FALSE itself=20
- the integer 0 (zero)=20
- the float 0.0 (zero)=20
- the empty string, and the string "0"=20
- an array with zero elements=20
- an object with zero member variables (PHP 4 only)
- the special type NULL (including unset variables)" ("Booleans",
php.net, 05 Dec. 2008).

If you were going to code it in the database, I'd suggest something like
this:

--Using T-SQL here...
DECLARE @TestNullValue SMALLDATETIME

SET @TestNullValue =3D NULL

If (@TestNullValue Is Null)=20
PRINT 'Evaluates to true!';
ELSE
PRINT 'Evaluates to false!';

In either case, this should have the same net result.

Be Well,
A-

-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]=20
Sent: Monday, December 08, 2008 5:10 PM
To: OKi98
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] MySQL Conditional Trigger


> anything compared to NULL is always false

Actually it's null.

mysql> select false =3D null;
+--------------+
| false =3D null |
+--------------+
| NULL |
+--------------+
1 row in set (0.01 sec)

mysql> select 1 =3D null;
+----------+
| 1 =3D null |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)

mysql> select 2 =3D null;
+----------+
| 2 =3D null |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)


unknown compared to anything is unknown.

--=20
Postgresql & php tutorials
http://www.designmagick.com/


--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

Fortuno, Adam wrote:
> All,
>
> Chris makes a good point i.e., "unknown compared to anything is
> unknown". His comment made me want to clarify my earlier note. In my
> earlier example, I wasn't suggesting that-that logic be coded in the
> database. I was assuming it would be in the page:
>
> >
> $TestNullValue = NULL;
>
> If ($TestNullValue) {
> print "Evaluates to true!";
> } else {
> print "Evaluates to false!";
> }
>
> ?>
>
> I tested this, and it worked. Here is why, PHP (like pretty much every
> other language) silently casts a NULL to false:
>
> "When converting to boolean, the following values are considered FALSE:
>
> - the boolean FALSE itself
> - the integer 0 (zero)
> - the float 0.0 (zero)
> - the empty string, and the string "0"
> - an array with zero elements
> - an object with zero member variables (PHP 4 only)
> - the special type NULL (including unset variables)" ("Booleans",
> php.net, 05 Dec. 2008).

If you use the shortcut php check, but you can do it the longer way.

if ($value !== null) {
....
}

Can you turn off email notifications too please? It's rather annoying
when it's on a public mailing list.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

-------- Original Message --------
Subject: Re: [PHP-DB] MySQL Conditional Trigger
From: Chris
To: OKi98
Date: 8.12.2008 23:09
>
>> anything compared to NULL is always false
>
> Actually it's null.
>
> mysql> select false = null;
> +--------------+
> | false = null |
> +--------------+
> | NULL |
> +--------------+
> 1 row in set (0.01 sec)
>
> mysql> select 1 = null;
> +----------+
> | 1 = null |
> +----------+
> | NULL |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select 2 = null;
> +----------+
> | 2 = null |
> +----------+
> | NULL |
> +----------+
> 1 row in set (0.00 sec)
>
>
> unknown compared to anything is unknown.
>
he was talking about plsql and condition evaluation (ends with true or
false), your point is absolutely useless.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

>> unknown compared to anything is unknown.
>>
> he was talking about plsql and condition evaluation (ends with true or
> false), your point is absolutely useless.

The context is in the database - my example is in the database. How is
that useless?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

Chris wrote:
>>> unknown compared to anything is unknown.
>>>
>> he was talking about plsql and condition evaluation (ends with true or
>> false), your point is absolutely useless.
>
> The context is in the database - my example is in the database. How is
> that useless?

In case you missed it, this is the context:

> 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


ie - you are wrong.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

You could try with the <> operator.
!=, <> Not equal operator

Or you could try it this way - ISNULL(col_or_data_to_check).
Example:
mysql> SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
1 row in set (0.02 sec)



Aleksander


Chris wrote:
> Chris wrote:
>>>> unknown compared to anything is unknown.
>>>>
>>> he was talking about plsql and condition evaluation (ends with true
>>> or false), your point is absolutely useless.
>>
>> The context is in the database - my example is in the database. How
>> is that useless?
>
> In case you missed it, this is the context:
>
> > 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
>
>
> ie - you are wrong.
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Conditional Trigger

Aleksandar Vojnovic wrote:
> You could try with the <> operator.
> !=, <> Not equal operator

Same issue as using '=':

mysql> select 2 <> null;
+-----------+
| 2 <> null |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

Comparing anything with null returns null. (I'm not asking for a
solution, I'm stating it as fact).

> Or you could try it this way - ISNULL(col_or_data_to_check).
> Example:
> mysql> SELECT ISNULL(1+1);
> +-------------+
> | ISNULL(1+1) |
> +-------------+
> | 0 |
> +-------------+
> 1 row in set (0.02 sec)

You can also use 'coalesce' to set default value for null columns.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators. html#function_coalesce

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.htm l

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php