Edit File: triggers_1011ext.inc
#====================================================================== # # Trigger Tests # (test case numbering refer to requirement document TP v1.1) #====================================================================== # WL#4084: enable disabled parts, 2007-11-15, hhunger USE test; --source suite/funcs_1/include/tb3.inc --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> eval load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/memory_tb3.txt' into table tb3; --disable_abort_on_error ############################################## ################ Section 3.5.10 ################# # Check on Trigger Activation ############################################## #Section 3.5.10.1 # Test case: Ensure that every trigger that should be activated by # every possible type of implicit insertion into its subject # table (INSERT into a view based on the subject table) is # indeed activated correctly #Section 3.5.10.2 # Test case: Ensure that every trigger that should be activated by every # possible type of implicit insertion into its subject table # (UPDATE into a view based on the subject table) is indeed # activated correctly #Section 3.5.10.3 # Test case: Ensure that every trigger that should be activated by every # possible type of implicit insertion into its subject table # (DELETE from a view based on the subject table) is indeed # activated correctly let $message= Testcase 3.5.10.1/2/3:; --source include/show_msg.inc Create view vw11 as select * from tb3 where f122 like 'Test 3.5.10.1/2/3%'; Create trigger trg1a before insert on tb3 for each row set new.f163=111.11; Create trigger trg1b after insert on tb3 for each row set @test_var='After Insert'; Create trigger trg1c before update on tb3 for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update'; Create trigger trg1d after update on tb3 for each row set @test_var='After Update'; Create trigger trg1e before delete on tb3 for each row set @test_var=5; Create trigger trg1f after delete on tb3 for each row set @test_var= 2* @test_var+7; #Section 3.5.10.1 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1); Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2); Insert into vw11 (f122, f151) values ('Not in View', 3); select f121, f122, f151, f163 from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151; --sorted_result select f121, f122, f151, f163 from vw11; select f121, f122, f151, f163 from tb3 where f122 like 'Not in View'; #Section 3.5.10.2 Update vw11 set f163=1; select f121, f122, f151, f163 from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151; --sorted_result select f121, f122, f151, f163 from vw11; #Section 3.5.10.3 set @test_var=0; Select @test_var as 'before delete'; delete from vw11 where f151=1; select f121, f122, f151, f163 from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151; --sorted_result select f121, f122, f151, f163 from vw11; Select @test_var as 'after delete'; #Cleanup --disable_warnings drop view vw11; drop trigger trg1a; drop trigger trg1b; drop trigger trg1c; drop trigger trg1d; drop trigger trg1e; drop trigger trg1f; delete from tb3 where f122 like 'Test 3.5.10.1/2/3%'; --enable_warnings #Section 3.5.10.4 # Test case: Ensure that every trigger that should be activated by every # possible type of implicit insertion into its subject table # (LOAD into the subject table) is indeed activated correctly let $message= Testcase 3.5.10.4:; --source include/show_msg.inc --replace_result $engine_type <engine_to_be_used> eval create table tb_load (f1 int, f2 char(25),f3 int) engine = $engine_type; Create trigger trg4 before insert on tb_load for each row set new.f3=-(new.f1 div 5), @counter= @counter+1; set @counter= 0; select @counter as 'Rows Loaded Before'; --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> eval load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t9.txt' into table tb_load; select @counter as 'Rows Loaded After'; Select * from tb_load order by f1 limit 10; #Cleanup --disable_warnings drop trigger trg4; drop table tb_load; --enable_warnings #Section 3.5.10.5 # Testcase: Ensure that every trigger that should be activated by every possible # type of implicit update of its subject table (e.g.a FOREIGN KEY SET # DEFAULT action or an UPDATE of a view based on the subject table) is # indeed activated correctly let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test); --source include/show_msg.inc #Section 3.5.10.6 # Testcase: Ensure that every trigger that should be activated by every possible # type of implicit deletion from its subject table (e.g.a FOREIGN KEY # CASCADE action or a DELETE from a view based on the subject table) is # indeed activated correctly let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test); --source include/show_msg.inc #Section 3.5.10.extra # Testcase: Ensure that every trigger that should be activated by every possible # type of implicit deletion from its subject table (e.g. an action performed # on the subject table from a stored procedure is indeed activated correctly let $message= Testcase 3.5.10.extra:; --source include/show_msg.inc --replace_result $engine_type <engine_to_be_used> eval create table t1_sp (var136 tinyint, var151 decimal) engine = $engine_type; create trigger trg before insert on t1_sp for each row set @counter=@counter+1; # declare continue handler for sqlstate '01000' set done = 1; delimiter //; create procedure trig_sp() begin declare done int default 0; declare var151 decimal; declare var136 tinyint; declare cur1 cursor for select f136, f151 from tb3; declare continue handler for sqlstate '01000' set done = 1; open cur1; fetch cur1 into var136, var151; wl_loop: WHILE NOT done DO insert into t1_sp values (var136, var151); fetch cur1 into var136, var151; END WHILE wl_loop; close cur1; end// delimiter ;// set @counter=0; select @counter; --error ER_SP_FETCH_NO_DATA call trig_sp(); select @counter; select count(*) from tb3; select count(*) from t1_sp; #Cleanup --disable_warnings drop procedure trig_sp; drop trigger trg; drop table t1_sp; --enable_warnings ################################## ########## Section 3.5.11 ######## # Check on Trigger Performance # ################################## #Section 3.5.11.1 # Testcase: Ensure that a set of complicated, interlocking triggers that are activated # by multiple trigger events on no fewer than 50 different tables with at least # 500,000 rows each, all work correctly, return the correct results, and have # the correct effects on the database. It is expected that the Services Provider # will use its own skills and experience in database testing to devise tables and # triggers that fulfill this requirement. let $message= Testcase 3.5.11.1 (implemented in trig_perf.test); --source include/show_msg.inc ########################################## # Other Scenasrios (not in requirements) # ########################################## # Testcase: y.y.y.2: # Checking for triggers starting triggers (no direct requirement) let $message= Testcase y.y.y.2: Check for triggers starting triggers; --source include/show_msg.inc use test; --disable_warnings drop table if exists t1; drop table if exists t2_1; drop table if exists t2_2; drop table if exists t2_3; drop table if exists t2_4; drop table if exists t3; --enable_warnings --replace_result $engine_type <engine_to_be_used> eval create table t1 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t2_1 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t2_2 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t2_3 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t2_4 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t3 (f1 integer) engine = $engine_type; insert into t1 values (1); delimiter //; create trigger tr1 after insert on t1 for each row BEGIN insert into t2_1 (f1) values (new.f1+1); insert into t2_2 (f1) values (new.f1+1); insert into t2_3 (f1) values (new.f1+1); insert into t2_4 (f1) values (new.f1+1); END// delimiter ;// create trigger tr2_1 after insert on t2_1 for each row insert into t3 (f1) values (new.f1+10); create trigger tr2_2 after insert on t2_2 for each row insert into t3 (f1) values (new.f1+100); create trigger tr2_3 after insert on t2_3 for each row insert into t3 (f1) values (new.f1+1000); create trigger tr2_4 after insert on t2_4 for each row insert into t3 (f1) values (new.f1+10000); #lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write; insert into t1 values (1); #unlock tables; select * from t3 order by f1; #Cleanup --disable_warnings drop trigger tr1; drop trigger tr2_1; drop trigger tr2_2; drop trigger tr2_3; drop trigger tr2_4; drop table t1, t2_1, t2_2, t2_3, t2_4, t3; --enable_warnings # Testcase: y.y.y.3: # Checking for circular trigger definitions let $message= Testcase y.y.y.3: Circular trigger reference; --source include/show_msg.inc use test; --disable_warnings drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4; --enable_warnings --replace_result $engine_type <engine_to_be_used> eval create table t1 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t2 (f2 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t3 (f3 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t4 (f4 integer) engine = $engine_type; insert into t1 values (0); create trigger tr1 after insert on t1 for each row insert into t2 (f2) values (new.f1+1); create trigger tr2 after insert on t2 for each row insert into t3 (f3) values (new.f2+1); create trigger tr3 after insert on t3 for each row insert into t4 (f4) values (new.f3+1); create trigger tr4 after insert on t4 for each row insert into t1 (f1) values (new.f4+1); # Bug#11896 Partial locking in case of recursive trigger definittions --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG insert into t1 values (1); select * from t1 order by f1; select * from t2 order by f2; select * from t3 order by f3; select * from t4 order by f4; #Cleanup --disable_warnings drop trigger tr1; drop trigger tr2; drop trigger tr3; drop trigger tr4; drop table t1; drop table t2; drop table t3; drop table t4; --enable_warnings #Section y.y.y.4 # Testcase: create recursive trigger/storedprocedures conditions let $message= Testcase y.y.y.4: Recursive trigger/SP references; --source include/show_msg.inc set @sql_mode='traditional'; --replace_result $engine_type <engine_to_be_used> eval create table t1_sp ( count integer, var136 tinyint, var151 decimal) engine = $engine_type; delimiter //; create procedure trig_sp() begin declare done int default 0; declare var151 decimal; declare var136 tinyint; declare cur1 cursor for select f136, f151 from tb3; declare continue handler for sqlstate '01000' set done = 1; set @counter= @counter+1; open cur1; fetch cur1 into var136, var151; wl_loop: WHILE NOT done DO insert into t1_sp values (@counter, var136, var151); fetch cur1 into var136, var151; END WHILE wl_loop; close cur1; end// delimiter ;// create trigger trg before insert on t1_sp for each row call trig_sp(); set @counter=0; select @counter; --error ER_SP_RECURSION_LIMIT call trig_sp(); select @counter; select count(*) from tb3; select count(*) from t1_sp; # check recursion will not work here: set @@max_sp_recursion_depth= 10; set @counter=0; select @counter; --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG call trig_sp(); select @counter; select count(*) from tb3; select count(*) from t1_sp; #Cleanup --disable_warnings drop procedure trig_sp; drop trigger trg; drop table t1_sp; --enable_warnings # Testcase: y.y.y.5: # Checking rollback of nested trigger definitions let $message= Testcase y.y.y.5: Rollback of nested trigger references; --source include/show_msg.inc set @@sql_mode='traditional'; use test; --disable_warnings drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4; --enable_warnings --replace_result $engine_type <engine_to_be_used> eval create table t1 (f1 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t2 (f2 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t3 (f3 integer) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> eval create table t4 (f4 tinyint) engine = $engine_type; --replace_result $engine_type <engine_to_be_used> show create table t1; insert into t1 values (1); create trigger tr1 after insert on t1 for each row insert into t2 (f2) values (new.f1+1); create trigger tr2 after insert on t2 for each row insert into t3 (f3) values (new.f2+1); create trigger tr3 after insert on t3 for each row insert into t4 (f4) values (new.f3+1000); set autocommit=0; start transaction; # Bug#32656 NDB: Duplicate key error aborts transaction in handler. # Doesn't talk back to SQL --error ER_WARN_DATA_OUT_OF_RANGE insert into t1 values (1); commit; select * from t1 order by f1; select * from t2 order by f2; select * from t3 order by f3; #Cleanup drop trigger tr1; drop trigger tr2; drop trigger tr3; drop table t1; drop table t2; drop table t3; drop table t4; DROP TABLE test.tb3;