Bug #11118 INSERT inside a trigger succeeds regardless of NOT NULL constraint
Submitted: 6 Jun 2005 17:31 Modified: 6 Jun 2005 17:43
Reporter: Guillaume Lebur Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.6-beta OS:Windows (Windows XP / SP2)
Assigned to: CPU Architecture:Any

[6 Jun 2005 17:31] Guillaume Lebur
Description:
Updating a row with SET NEW.xxx=NULL in a trigger will set the new value to NULL, even if there's a NOT NULL constraint on the column.

Does that mean the trigger fires *after* constraint checks have been made (regardless of its BEFORE or AFTER type) ?

How to repeat:
>CREATE TABLE `equipe` (
  `id_equipe` int(11) NOT NULL auto_increment,
  `nom` varchar(50) NOT NULL,
  `stade` varchar(50) NOT NULL,
  `ville` varchar(30) NOT NULL,
  `points` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id_equipe`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

> INSERT INTO equipe (nom, stade, ville) VALUES (NULL, NULL, NULL)

Error : Column 'nom' cannot be null

>CREATE TRIGGER TGR_BI_EQUIPE BEFORE INSERT ON equipe FOR EACH ROW
BEGIN
SET new.nom=null;
END

> INSERT INTO equipe (nom, stade, ville) VALUES ('x', 'y', 'z')

Query OK, 1 row affected, 1 warning (0.00 sec)

> SELECT * FROM equipe WHERE id_equipe=32

+-----------+-------+-------+-------+--------+
| id_equipe | nom | stade |  ville | points |
+-----------+-------+-------+-------+--------+
|        32   |         | y       | z      |        0 |
+-----------+-------+-------+-------+--------+
1 row in set (0.00 sec)
[6 Jun 2005 17:43] MySQL Verification Team
Testing against current Bk source, this issue don't exists anymore.
Thank you for the bug report:

mysql> CREATE TRIGGER TGR_BI_EQUIPE BEFORE INSERT ON equipe FOR EACH ROW
    -> BEGIN
    -> SET new.nom=null;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> INSERT INTO equipe (nom, stade, ville) VALUES ('x', 'y', 'z');
ERROR 1048 (23000): Column 'nom' cannot be null