NOT NULL checked before BEFORE TRIGGER

[Ondra Zizka]

Hi,

I have a DATE NOT NULL column. I also have a BEFORE INSERT TRIGGER, that should convert the value to NOW():

CREATE TABLE tab ( col DATE NOT NULL, col2 INT );

CREATE TRIGGER tab_bi
BEFORE INSERT ON tab
FOR EACH ROW SET NEW.col = NOW();

When I do

INSERT INTO tab SET col2 = 1;

MySQL 5.0 complains about NOT NULL constraint violation.

Logically, when it's _before_ insert, I would expect it to be done _before_, and the check should happen when the trigger has set the value to NOW().

Is this a bug, or is it SQL-standard conformal? Or what is the reason for this.

Thanks, Ondra Zizka