diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc')
| -rw-r--r-- | mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc | 430 |
1 files changed, 430 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc new file mode 100644 index 00000000..88b04a0e --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc @@ -0,0 +1,430 @@ +#====================================================================== +# +# 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 + set sql_mode = 'NO_ENGINE_SUBSTITUTION'; + --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 + set sql_mode = default; + +################################## +########## 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 = 'NO_ENGINE_SUBSTITUTION'; + --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 + set sql_mode = default; + + +# 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; + --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; |