Description:
When comparing a field value's NEW and OLD value in an update trigger you sometimes get wrong results.
How to repeat:
Change the value of the field "datum" to a non NULL value -> comparison gives wrong result and no value is entered into table2. Change value back to NULL -> value is entered into table2.
/*
SQLyog Enterprise - MySQL GUI v7.02
MySQL - 5.0.67-log : Database - test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*Table structure for table `table1` */
CREATE TABLE `table1` (
`id` int(11) unsigned NOT NULL auto_increment,
`datum` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*Data for the table `table1` */
insert into `table1`(`id`,`datum`) values (1,NULL);
/*Table structure for table `table2` */
CREATE TABLE `table2` (
`id` int(11) unsigned NOT NULL auto_increment,
`result` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `table2` */
/* Trigger structure for table `table1` */
DELIMITER $$
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `table1_after_upd_tr` AFTER UPDATE ON `table1` FOR EACH ROW BEGIN
IF NOT (NEW.datum <=> OLD.datum) THEN
INSERT INTO table2 VALUES(NULL,1);
END IF;
END */$$
DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
Description: When comparing a field value's NEW and OLD value in an update trigger you sometimes get wrong results. How to repeat: Change the value of the field "datum" to a non NULL value -> comparison gives wrong result and no value is entered into table2. Change value back to NULL -> value is entered into table2. /* SQLyog Enterprise - MySQL GUI v7.02 MySQL - 5.0.67-log : Database - test ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*Table structure for table `table1` */ CREATE TABLE `table1` ( `id` int(11) unsigned NOT NULL auto_increment, `datum` date default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; /*Data for the table `table1` */ insert into `table1`(`id`,`datum`) values (1,NULL); /*Table structure for table `table2` */ CREATE TABLE `table2` ( `id` int(11) unsigned NOT NULL auto_increment, `result` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `table2` */ /* Trigger structure for table `table1` */ DELIMITER $$ /*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `table1_after_upd_tr` AFTER UPDATE ON `table1` FOR EACH ROW BEGIN IF NOT (NEW.datum <=> OLD.datum) THEN INSERT INTO table2 VALUES(NULL,1); END IF; END */$$ DELIMITER ; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;