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 ('-');
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 ('-');