diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/trigger_wl3253.test | 428 |
1 files changed, 428 insertions, 0 deletions
diff --git a/mysql-test/main/trigger_wl3253.test b/mysql-test/main/trigger_wl3253.test new file mode 100644 index 00000000..3504eeaf --- /dev/null +++ b/mysql-test/main/trigger_wl3253.test @@ -0,0 +1,428 @@ +--echo # +--echo # WL#3253: multiple triggers per table +--echo # + +SET @binlog_format_saved = @@binlog_format; +SET binlog_format=ROW; +SET time_zone='+00:00'; + +--echo # +--echo # Test 1. +--echo # Check that the sequence of triggers for the same combination +--echo # of event type/action type can be created for a table +--echo # and is fired consequently in the order of its creation +--echo # during statement execution. +--echo # In this test we check BEFORE triggers. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); + +INSERT INTO t1 VALUES (1); + +SELECT * FROM t2 ORDER BY b; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Test 2. +--echo # Check that the sequence of triggers for the same combination +--echo # of event type/action type can be created for a table +--echo # and is fired consequently in the order of its creation +--echo # during statement execution. +--echo # In this test we check AFTER triggers. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); + +INSERT INTO t1 VALUES (1); + +SELECT * FROM t2 ORDER BY b; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Test 3. +--echo # Check that the sequences of triggers for the different event types +--echo # can be created for a table and are fired consequently +--echo # in the order of its creation during statement execution. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); + +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); + +INSERT INTO t1 VALUES (1); + +SELECT * FROM t2 ORDER BY b; + +UPDATE t1 SET a = 5; + +SELECT * FROM t2 ORDER BY b; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Test 4. +--echo # Check that every new created trigger has unique action_order value +--echo # started from 1 and NOT NULL value for creation timestamp. +--echo # + +CREATE TABLE t1 (a INT); + +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; + +SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; + +--echo # +--echo # Test 5. +--echo # Check that action_order attribute isn't shown +--echo # in the output of SHOW TRIGGERS and SHOW CREATE TRIGGER +--echo # + +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; + +--replace_column 6 # +SHOW TRIGGERS; + +--replace_column 17 # +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; + +--replace_column 7 # +SHOW CREATE TRIGGER tr1_bi; + +DROP TABLE t1; + +--echo # +--echo # Test 6. +--echo # Check that action_order attribute is reused when trigger +--echo # are recreated. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TRIGGER tr1_bi; + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TABLE t1; + +--echo # +--echo # Test 7. +--echo # Check that it is possible to create several triggers with +--echo # the same value for creation timestamp. +--echo # + +CREATE TABLE t1 (a INT); + +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; + +SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; + +--echo # +--echo # Test 8. +--echo # Check that SHOW CREATE TRIGGER outputs the CREATED attribute +--echo # and it is not NULL +--echo # + +CREATE TABLE t1 (a INT); + +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SHOW CREATE TRIGGER tr1_bi; + +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; + +--echo # +--echo # Test 9. +--echo # Check that SHOW TRIGGERS outputs the CREATED attribute +--echo # and it is not NULL. +--echo # + +CREATE TABLE t1 (a INT); + +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; + +SHOW TRIGGERS; + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; + +DROP TABLE t1; + +SET TIMESTAMP=DEFAULT; + +--echo # +--echo # Test 10. +--echo # Check that FOLLOWS clause is supported and works correctly. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi INSERT INTO t2 (a) VALUES (NEW.a + 200); + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Test 11. +--echo # Check that PRECEDES clause is supported and works correctly. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr3_bi INSERT INTO t2 (a) VALUES (NEW.a + 200); + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Test 12. +--echo # Check that the PRECEDES works properly for the 1st trigger in the chain. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi INSERT INTO t2 (a) VALUES (NEW.a); + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Test 13. +--echo # Check that error is reported if the FOLLOWS clause references to +--echo # non-existing trigger +--echo # + +CREATE TABLE t1 (a INT); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3; + +--error ER_REFERENCED_TRG_DOES_NOT_EXIST +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr0_bi SET @a:=2; + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TABLE t1; + +--echo # +--echo # Test 14. +--echo # Check that error is reported if the PRECEDES clause references to +--echo # non-existing trigger +--echo # + +CREATE TABLE t1 (a INT); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3; + +--error ER_REFERENCED_TRG_DOES_NOT_EXIST +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr0_bi SET @a:=2; + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TABLE t1; + +--echo # +--echo # Test 15. +--echo # Check that action_order value is independent for each type of event +--echo # (INSERT/UPDATE/DELETE) +--echo # + +CREATE TABLE t1 (a INT); + +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr2_bi SET @a:=3; +CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3; + +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; + +DROP TABLE t1; + +--echo # +--echo # Test 16. +--echo # Check that the trigger in the clause FOLLOWS/PRECEDES can refences +--echo # only to the trigger for the same ACTION/TIMINMG +--echo # + +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; + +--error ER_REFERENCED_TRG_DOES_NOT_EXIST +CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; + +--error ER_REFERENCED_TRG_DOES_NOT_EXIST +CREATE TRIGGER tr2_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; + +--error ER_REFERENCED_TRG_DOES_NOT_EXIST +CREATE TRIGGER tr1_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3; + +--error ER_REFERENCED_TRG_DOES_NOT_EXIST +CREATE TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; + +--replace_column 6 # +SHOW TRIGGERS; + +--replace_column 17 # +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; + +DROP TABLE t1; + +# Binlog is required +--source include/have_log_bin.inc + +--echo # +--echo # Test 17. Check that table's triggers are dumped correctly. +--echo # +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; + +# dump tables and triggers +--exec $MYSQL_DUMP --compact test + +DROP TABLE t1; + +--echo # +--echo # Test 18. Check that table's triggers are dumped in right order +--echo # taking into account the PRECEDES/FOLLOWS clauses. +--echo # + +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a:=0; +CREATE TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=0; + +--echo # Expected order of triggers in the dump is: tr0_bi, tr1_bi, tr1_1_bi, tr2_i. +# dump tables and triggers +--exec $MYSQL_DUMP --compact test + +DROP TABLE t1; + +--echo # +--echo # Test 19. Check that table's triggers are dumped correctly in xml. +--echo # + +CREATE TABLE t1 (a INT); +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; +SET TIMESTAMP=DEFAULT; + +# dump tables and triggers +--exec $MYSQL_DUMP --compact --no-create-info --xml test + +DROP TABLE t1; + +--echo # +--echo # Test 20. Check that the statement CHECK TABLE FOR UPGRADE outputs +--echo # the warnings for triggers created by a server without support for wl3253. +--echo # + +CREATE TABLE t1 (a INT); + +let $MYSQLD_DATADIR=`SELECT @@datadir`; +--write_file $MYSQLD_DATADIR/test/t1.TRG +TYPE=TRIGGERS +triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW SET @a:=2' +sql_modes=1073741824 1073741824 +definers='root@localhost' 'root@localhost' +client_cs_names='latin1' 'latin1' +connection_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' +db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' +EOF + +--write_file $MYSQLD_DATADIR/test/tr1_bi.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr1_ai.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +FLUSH TABLE t1; + +CHECK TABLE t1 FOR UPGRADE; + +SHOW TRIGGERS; + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; + +SHOW CREATE TRIGGER tr1_bi; +SHOW CREATE TRIGGER tr1_ai; + +DROP TABLE t1; + +SET binlog_format=@binlog_format_saved; + +--echo # End of tests. +--echo # |