MySQL Forums
Forum List  »  Triggers

After update trigger result different with 5.0 and 5.1
Posted by: Yung-Chi Yang
Date: March 23, 2017 09:12PM

Hi,

We are planning to upgrade our old MySQL system from 5.0.96 to 5.1.73.
but after upgraded we found some problem on the trigger function.

We have a trigger that will insert new record into BB table after AA updated.
But we found if the update action on AA table but no row been changed.
Then BB table be inserted new record in MySQL 5.1.73.
But in MySQL 5.0.96 no row be inserted.

There is a simple test case below and could found the difference easily.

mysql> CREATE TABLE AA (a INT, b INT);
mysql> CREATE TABLE BB (a INT, b INT);
mysql> INSERT INTO AA VALUES(1,1);
mysql> INSERT INTO AA VALUES(2,2);
mysql> INSERT INTO AA VALUES(3,3);
mysql> CREATE TRIGGER test_trigger AFTER UPDATE ON AA FOR EACH ROW
-> INSERT INTO BB VALUES(NEW.a, NEW.b);

mysql> UPDATE AA SET b=3 WHERE a=3;

****** the result on MySQL 5.1.73 *****

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT * FROM BB;
+------+------+
| a | b |
+------+------+
| 3 | 3 |
+------+------+
1 row in set (0.00 sec)

***** the result on MySQL 5.0.96 *****

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT * FROM BB;
Empty set (0.00 sec)

We want to know how to make MySQL 5.1.73 have the some result with 5.0.96.
Is there anybody to could help us to solve this problem?

Thanks.

Best Regard,
Rio

Options: ReplyQuote


Subject
Views
Written By
Posted
After update trigger result different with 5.0 and 5.1
1223
March 23, 2017 09:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.