diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/trigger.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 33 |
2 files changed, 53 insertions, 0 deletions
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 2290919f191..23f15f618f2 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1960,6 +1960,26 @@ select * from t2; s1 drop table t1; drop temporary table t2; +#------------------------------------------------------------------------ +# Bug#39953 Triggers are not working properly with multi table updates +#------------------------------------------------------------------------ +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS t_insert; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a)); +INSERT INTO t1 (a) VALUES (2),(5); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END | +INSERT INTO t2 (a,b) VALUES (1,2); +DROP TRIGGER t_insert; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END | +INSERT INTO t2 (a,b) VALUES (3,5); +ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +DROP TABLE t1; +DROP TRIGGER t_insert; +DROP TABLE t2; End of 5.0 tests drop table if exists table_25411_a; drop table if exists table_25411_b; diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 6974a4cc5da..9a5556c518d 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2218,6 +2218,39 @@ select * from t1; select * from t2; drop table t1; drop temporary table t2; + +--echo #------------------------------------------------------------------------ +--echo # Bug#39953 Triggers are not working properly with multi table updates +--echo #------------------------------------------------------------------------ + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS t_insert; +DROP TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a)); +INSERT INTO t1 (a) VALUES (2),(5); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +INSERT INTO t2 (a,b) VALUES (1,2); + +DROP TRIGGER t_insert; + +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO t2 (a,b) VALUES (3,5); + +DROP TABLE t1; +DROP TRIGGER t_insert; +DROP TABLE t2; + --echo End of 5.0 tests # |