Bug #39660 Before Trigger in combination with NOT NULL behaves wrong
Submitted: 26 Sep 2008 8:13 Modified: 26 Sep 2008 8:55
Reporter: Susanne Ebrecht Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[26 Sep 2008 8:13] Susanne Ebrecht 
Description:
CREATE TABLE t(i INTEGER NOT NULL);

DELIMITER §

CREATE TRIGGER tr_null BEFORE INSERT ON t
FOR EACH ROW BEGIN
IF NEW.i IS NULL THEN
SET NEW.i = 0;
END IF;
END§

DELIMITER ;

INSERT INTO t(i) VALUES(NULL);

ERROR 1048 (23000): Column 'i' cannot be null 

How to repeat:
See above

Suggested fix:
The check of not null should be after the trigger get executed not before.
[26 Sep 2008 8:55] Susanne Ebrecht 
This is a duplicate of bug #6295.
[2 Jan 2009 16:45] Olav Schettler 
I have also encountered this annoying bug on my server version: 5.0.51a.

My application introduces a new field "created INT(11) NOT NULL". My before-trigger to update this field does not fire because the legacy application that runs on the same data does a "SET created = NULL" and thus throws an error.