Description:
A trigger filtering data and replacing values of incorrect datatypes isn't allowed to modify the data before the datatype checking for the column itself can abort it.
See example:
eeyore> CREATE TABLE trigtest ( a INT );
Query OK, 0 rows affected (0.01 sec)
eeyore> CREATE TRIGGER trigtest BEFORE INSERT ON trigtest FOR EACH ROW SET NEW.a = IF(NEW.a = '-', NULL, NEW.a);
Query OK, 0 rows affected (0.02 sec)
eeyore> INSERT INTO trigtest VALUES ('-');
Query OK, 1 row affected, 1 warning (0.02 sec)
eeyore> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
eeyore> SELECT * FROM trigtest;
+------+
| a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
eeyore> SET SQL_MODE = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.02 sec)
eeyore> INSERT INTO trigtest VALUES ('-');
ERROR 1264 (22003): Out of range value for column 'a' at row 1
So, you can modify the rows inserted, but not if you also want strict mode, and if you run in non-strict mode, you get warnings, despite your data being correct and the trigger firing off perfectly fine.
How to repeat:
-- Copy and paste friendly.
CREATE TABLE trigtest ( a INT );
CREATE TRIGGER trigtest BEFORE INSERT ON trigtest FOR EACH ROW SET NEW.a = IF(NEW.a = '-', NULL, NEW.a);
INSERT INTO trigtest VALUES ('-');
SHOW WARNINGS;
SELECT * FROM trigtest;
SET SQL_MODE = 'STRICT_ALL_TABLES';
INSERT INTO trigtest VALUES ('-');