Re: trigger not working on same database on two versions of mysql

[Liviu Vasut]

I tried it just now, but with no success. After more reading I found out that setting ERROR_FOR_DIVISION_BY_ZERO mode doesn't do what I need unless I add the strict mode also. Here is a quote from the manual:
- If strict mode is not enabled, a warning occurs.
- If strict mode is enabled, inserts and updates involving division by zero are prohibited, and an error occurs.
I did what they said but it only works for inserts not updates, just like before. It's really annoying :(.

mysql> set sql_mode='strict_all_tables,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------+
| @@sql_mode |
+----------------------------------------------+
| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> update goods set quantity=-1 where id=0;
Query OK, 1 row affected, 1 warning (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> show warnings;
+---------+------+----------------------------+
| Level | Code | Message |
+---------+------+----------------------------+
| Warning | 1048 | Column 'id' cannot be null |
+---------+------+----------------------------+
1 row in set (0.00 sec)