diff options
| -rw-r--r-- | mysql-test/r/trigger.result | 41 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 38 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 12 |
3 files changed, 85 insertions, 6 deletions
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 diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 5c95004c901..49b7b527bd9 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1699,4 +1699,42 @@ DROP TABLE bug22580_t1; DROP PROCEDURE bug22580_proc_1; DROP PROCEDURE bug22580_proc_2; +# +# Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY +# UPDATE if the row wasn't actually changed. +# +--disable_warnings +DROP TRIGGER IF EXISTS trg27006_a_update; +DROP TRIGGER IF EXISTS trg27006_a_insert; +--enable_warnings + +CREATE TABLE t1 ( + `id` int(10) unsigned NOT NULL auto_increment, + `val` varchar(10) NOT NULL, + PRIMARY KEY (`id`) +); +CREATE TABLE t2 like t1; +DELIMITER |; + +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 | +DELIMITER ;| + +INSERT INTO t1(val) VALUES ('test1'),('test2'); +SELECT * FROM t1; +SELECT * FROM t2; +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; +SELECT * FROM t2; +DROP TRIGGER trg27006_a_insert; +DROP TRIGGER trg27006_a_update; +drop table t1,t2; + --echo End of 5.0 tests diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 35e724beec8..dd08ccef462 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1238,19 +1238,19 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (table->next_number_field) table->file->adjust_next_insert_id_after_explicit_value( table->next_number_field->val_int()); - info->touched++; + info->touched++; if ((table->file->table_flags() & HA_PARTIAL_COLUMN_READ) || compare_record(table, thd->query_id)) { info->updated++; - - trg_error= (table->triggers && - table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, - TRG_ACTION_AFTER, - TRUE)); info->copied++; } + + trg_error= (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, + TRG_ACTION_AFTER, + TRUE)); goto ok_or_after_trg_err; } else /* DUP_REPLACE */ |