When inserting a new row into a table without specifying a column that is set to not null will give the error "Field 'xxx' doesn't have a default value" - even if a trigger exists, that should replace the NULL value with a not-null value before inserting it.
Triggers declared as "before insert on 'table_xxx' " should be fired if they might change any of the new values to insert BEFORE column constrains are verified.
See similar SQL bug that has been fixed:
If a new row contains a null value for a column that is defined as not null but is changed in a trigger, that trigger won't be fired at all as immediately a error is thrown.
Example SQL code to reproduce the error:
- define column "NULLABLE" without "NOT NULL" constraint.
- Insert keyword instead of NULL value. For example -1 or 'NULL'
- Inside Trigger: "if new.column = -1" or " if new.column = 'NULL' " then do something
Example SQL code to demonstrate the workaround:
(Note: Only the insert statement as well as the comparison in the trigger (if new.rate is null) changed.)