1--echo # 2--echo # WL#3253: multiple triggers per table 3--echo # 4 5SET @binlog_format_saved = @@binlog_format; 6SET binlog_format=ROW; 7 8--echo # 9--echo # Test 1. 10--echo # Check that the sequence of triggers for the same combination 11--echo # of event type/action type can be created for a table 12--echo # and is fired consequently in the order of its creation 13--echo # during statement execution. 14--echo # In this test we check BEFORE triggers. 15--echo # 16 17CREATE TABLE t1 (a INT); 18CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); 19 20CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); 21CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); 22 23INSERT INTO t1 VALUES (1); 24 25SELECT * FROM t2 ORDER BY b; 26 27DROP TABLE t2; 28DROP TABLE t1; 29 30--echo # 31--echo # Test 2. 32--echo # Check that the sequence of triggers for the same combination 33--echo # of event type/action type can be created for a table 34--echo # and is fired consequently in the order of its creation 35--echo # during statement execution. 36--echo # In this test we check AFTER triggers. 37--echo # 38 39CREATE TABLE t1 (a INT); 40CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); 41 42CREATE TRIGGER tr1_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); 43CREATE TRIGGER tr2_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); 44 45INSERT INTO t1 VALUES (1); 46 47SELECT * FROM t2 ORDER BY b; 48 49DROP TABLE t2; 50DROP TABLE t1; 51 52--echo # 53--echo # Test 3. 54--echo # Check that the sequences of triggers for the different event types 55--echo # can be created for a table and are fired consequently 56--echo # in the order of its creation during statement execution. 57--echo # 58 59CREATE TABLE t1 (a INT); 60CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); 61 62CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); 63CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); 64 65CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); 66CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); 67 68INSERT INTO t1 VALUES (1); 69 70SELECT * FROM t2 ORDER BY b; 71 72UPDATE t1 SET a = 5; 73 74SELECT * FROM t2 ORDER BY b; 75 76DROP TABLE t2; 77DROP TABLE t1; 78 79--echo # 80--echo # Test 4. 81--echo # Check that every new created trigger has unique action_order value 82--echo # started from 1 and NOT NULL value for creation timestamp. 83--echo # 84 85CREATE TABLE t1 (a INT); 86 87SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00'); 88CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 89SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); 90CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 91 92SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 93 94DROP TABLE t1; 95SET TIMESTAMP=DEFAULT; 96 97--echo # 98--echo # Test 5. 99--echo # Check that action_order attribute isn't shown 100--echo # in the output of SHOW TRIGGERS and SHOW CREATE TRIGGER 101--echo # 102 103CREATE TABLE t1 (a INT); 104CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 105 106--replace_column 6 # 107SHOW TRIGGERS; 108 109--replace_column 17 # 110SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; 111 112--replace_column 7 # 113SHOW CREATE TRIGGER tr1_bi; 114 115DROP TABLE t1; 116 117--echo # 118--echo # Test 6. 119--echo # Check that action_order attribute is reused when trigger 120--echo # are recreated. 121--echo # 122 123CREATE TABLE t1 (a INT); 124CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 125 126SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 127 128DROP TRIGGER tr1_bi; 129 130CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 131 132SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 133 134DROP TABLE t1; 135 136--echo # 137--echo # Test 7. 138--echo # Check that it is possible to create several triggers with 139--echo # the same value for creation timestamp. 140--echo # 141 142CREATE TABLE t1 (a INT); 143 144SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); 145CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 146CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 147 148SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 149 150DROP TABLE t1; 151SET TIMESTAMP=DEFAULT; 152 153--echo # 154--echo # Test 8. 155--echo # Check that SHOW CREATE TRIGGER outputs the CREATED attribute 156--echo # and it is not NULL 157--echo # 158 159CREATE TABLE t1 (a INT); 160 161SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); 162CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 163SHOW CREATE TRIGGER tr1_bi; 164 165DROP TABLE t1; 166SET TIMESTAMP=DEFAULT; 167 168--echo # 169--echo # Test 9. 170--echo # Check that SHOW TRIGGERS outputs the CREATED attribute 171--echo # and it is not NULL. 172--echo # 173 174CREATE TABLE t1 (a INT); 175 176SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); 177 178CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 179 180SHOW TRIGGERS; 181 182SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; 183 184DROP TABLE t1; 185 186SET TIMESTAMP=DEFAULT; 187 188--echo # 189--echo # Test 10. 190--echo # Check that FOLLOWS clause is supported and works correctly. 191--echo # 192 193CREATE TABLE t1 (a INT); 194CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); 195 196CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); 197CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); 198CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi INSERT INTO t2 (a) VALUES (NEW.a + 200); 199 200SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 201 202INSERT INTO t1 VALUES (1); 203SELECT * FROM t2 ORDER BY b; 204 205DROP TABLE t2; 206DROP TABLE t1; 207 208--echo # 209--echo # Test 11. 210--echo # Check that PRECEDES clause is supported and works correctly. 211--echo # 212 213CREATE TABLE t1 (a INT); 214CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); 215 216CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); 217CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); 218CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr3_bi INSERT INTO t2 (a) VALUES (NEW.a + 200); 219 220SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 221 222INSERT INTO t1 VALUES (1); 223SELECT * FROM t2 ORDER BY b; 224 225DROP TABLE t2; 226DROP TABLE t1; 227 228--echo # 229--echo # Test 12. 230--echo # Check that the PRECEDES works properly for the 1st trigger in the chain. 231--echo # 232 233CREATE TABLE t1 (a INT); 234CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); 235 236CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); 237CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi INSERT INTO t2 (a) VALUES (NEW.a); 238 239SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 240 241INSERT INTO t1 VALUES (1); 242SELECT * FROM t2 ORDER BY b; 243 244DROP TABLE t2; 245DROP TABLE t1; 246 247--echo # 248--echo # Test 13. 249--echo # Check that error is reported if the FOLLOWS clause references to 250--echo # non-existing trigger 251--echo # 252 253CREATE TABLE t1 (a INT); 254 255CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 256CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3; 257 258--error ER_REFERENCED_TRG_DOES_NOT_EXIST 259CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr0_bi SET @a:=2; 260 261SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 262 263DROP TABLE t1; 264 265--echo # 266--echo # Test 14. 267--echo # Check that error is reported if the PRECEDES clause references to 268--echo # non-existing trigger 269--echo # 270 271CREATE TABLE t1 (a INT); 272 273CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 274CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3; 275 276--error ER_REFERENCED_TRG_DOES_NOT_EXIST 277CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr0_bi SET @a:=2; 278 279SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 280 281DROP TABLE t1; 282 283--echo # 284--echo # Test 15. 285--echo # Check that action_order value is independent for each type of event 286--echo # (INSERT/UPDATE/DELETE) 287--echo # 288 289CREATE TABLE t1 (a INT); 290 291CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 292CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 293CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; 294 295SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 296 297CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr2_bi SET @a:=3; 298CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3; 299 300SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; 301 302DROP TABLE t1; 303 304--echo # 305--echo # Test 16. 306--echo # Check that the trigger in the clause FOLLOWS/PRECEDES can refences 307--echo # only to the trigger for the same ACTION/TIMINMG 308--echo # 309 310CREATE TABLE t1 (a INT); 311CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 312CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; 313 314--error ER_REFERENCED_TRG_DOES_NOT_EXIST 315CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; 316 317--error ER_REFERENCED_TRG_DOES_NOT_EXIST 318CREATE TRIGGER tr2_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; 319 320--error ER_REFERENCED_TRG_DOES_NOT_EXIST 321CREATE TRIGGER tr1_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3; 322 323--error ER_REFERENCED_TRG_DOES_NOT_EXIST 324CREATE TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; 325 326--replace_column 6 # 327SHOW TRIGGERS; 328 329--replace_column 17 # 330SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; 331 332DROP TABLE t1; 333 334# Binlog is required 335--source include/have_log_bin.inc 336 337--echo # 338--echo # Test 17. Check that table's triggers are dumped correctly. 339--echo # 340CREATE TABLE t1 (a INT); 341CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 342CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 343CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; 344 345# dump tables and triggers 346--exec $MYSQL_DUMP --compact test 347 348DROP TABLE t1; 349 350--echo # 351--echo # Test 18. Check that table's triggers are dumped in right order 352--echo # taking into account the PRECEDES/FOLLOWS clauses. 353--echo # 354 355CREATE TABLE t1 (a INT); 356CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 357CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 358CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a:=0; 359CREATE TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=0; 360 361--echo # Expected order of triggers in the dump is: tr0_bi, tr1_bi, tr1_1_bi, tr2_i. 362# dump tables and triggers 363--exec $MYSQL_DUMP --compact test 364 365DROP TABLE t1; 366 367--echo # 368--echo # Test 19. Check that table's triggers are dumped correctly in xml. 369--echo # 370 371CREATE TABLE t1 (a INT); 372SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00'); 373CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; 374CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; 375CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; 376SET TIMESTAMP=DEFAULT; 377 378# dump tables and triggers 379--exec $MYSQL_DUMP --compact --no-create-info --xml test 380 381DROP TABLE t1; 382 383--echo # 384--echo # Test 20. Check that the statement CHECK TABLE FOR UPGRADE outputs 385--echo # the warnings for triggers created by a server without support for wl3253. 386--echo # 387 388CREATE TABLE t1 (a INT); 389 390let $MYSQLD_DATADIR=`SELECT @@datadir`; 391--write_file $MYSQLD_DATADIR/test/t1.TRG 392TYPE=TRIGGERS 393triggers='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' 394sql_modes=1073741824 1073741824 395definers='root@localhost' 'root@localhost' 396client_cs_names='latin1' 'latin1' 397connection_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' 398db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' 399EOF 400 401--write_file $MYSQLD_DATADIR/test/tr1_bi.TRN 402TYPE=TRIGGERNAME 403trigger_table=t1 404EOF 405 406--write_file $MYSQLD_DATADIR/test/tr1_ai.TRN 407TYPE=TRIGGERNAME 408trigger_table=t1 409EOF 410 411FLUSH TABLE t1; 412 413CHECK TABLE t1 FOR UPGRADE; 414 415SHOW TRIGGERS; 416 417SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; 418 419SHOW CREATE TRIGGER tr1_bi; 420SHOW CREATE TRIGGER tr1_ai; 421 422DROP TABLE t1; 423 424SET binlog_format=@binlog_format_saved; 425 426--echo # End of tests. 427--echo # 428