From 6d93f15039d551f291232c1b60527b00cd9c6bc9 Mon Sep 17 00:00:00 2001
From: unknown <evgen@moonbone.local>
Date: Fri, 16 Mar 2007 17:23:26 +0300
Subject: Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON
 DUPLICATE KEY UPDATE if the row wasn't actually changed.

This bug was caused by fix for bug#19978. It causes AFTER UPDATE triggers
not firing if a row wasn't actually changed by the update part of the
INSERT .. ON DUPLICATE KEY UPDATE.

Now triggers are always fired if a row is touched by the INSERT ... ON
DUPLICATE KEY UPDATE.


sql/sql_insert.cc:
  Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
  UPDATE if the row wasn't actually changed.
  Now triggers are always fired if a row is touched by the INSERT ... ON
  DUPLICATE KEY UPDATE.
mysql-test/r/trigger.result:
  Added a test case for the bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
  UPDATE if the row wasn't actually changed.
mysql-test/t/trigger.test:
  Added a test case for the bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
  UPDATE if the row wasn't actually changed.
---
 mysql-test/r/trigger.result | 41 +++++++++++++++++++++++++++++++++++++++++
 1 file changed, 41 insertions(+)

(limited to 'mysql-test/r/trigger.result')

diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index aa511ca27a7..da72973dc52 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1372,4 +1372,45 @@ INSERT INTO bug22580_t1 VALUES (1,1);
 DROP TABLE bug22580_t1;
 DROP PROCEDURE bug22580_proc_1;
 DROP PROCEDURE bug22580_proc_2;
+DROP TRIGGER IF EXISTS trg27006_a_update;
+DROP TRIGGER IF EXISTS trg27006_a_insert;
+CREATE TABLE t1 (
+`id` int(10) unsigned NOT NULL auto_increment,
+`val` varchar(10) NOT NULL,
+PRIMARY KEY  (`id`)
+);
+CREATE TABLE t2 like t1;
+CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+insert into t2 values (NULL,new.val);
+END |
+CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW
+BEGIN
+insert into t2 values (NULL,new.val);
+END |
+INSERT INTO t1(val) VALUES ('test1'),('test2');
+SELECT * FROM t1;
+id	val
+1	test1
+2	test2
+SELECT * FROM t2;
+id	val
+1	test1
+2	test2
+INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val);
+INSERT INTO t1 VALUES (3,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);
+SELECT * FROM t1;
+id	val
+1	test1
+2	test2
+3	test3
+SELECT * FROM t2;
+id	val
+1	test1
+2	test2
+3	test2
+4	test3
+DROP TRIGGER trg27006_a_insert;
+DROP TRIGGER trg27006_a_update;
+drop table t1,t2;
 End of 5.0 tests
-- 
cgit v1.2.1