#====================================================================== # # Trigger Tests # (test case numbering refer to requirement document TP v1.1) #====================================================================== --disable_abort_on_error # General setup for Trigger tests let $message= Testcase: 3.5:; --source include/show_msg.inc --disable_abort_on_error create User test_general@localhost; set password for test_general@localhost = password('PWD'); revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost; create User test_super@localhost; set password for test_super@localhost = password('PWD'); grant ALL on *.* to test_super@localhost with grant OPTION; --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK connect (con1_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK connect (con1_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection default; #################################### ############ Section 3.5.4 ######### # Drop Trigger Checkes: # #################################### let $message= Testcase 3.5.4:; --source include/show_msg.inc connection default; use test; #Section 3.5.4.1 # Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger. let $message= Testcase 3.5.4.1:; --source include/show_msg.inc connection con1_super; create database db_drop; Use db_drop; eval create table t1 (f1 char(30)) engine=$engine_type; grant INSERT, SELECT on db_drop.t1 to test_general; Use db_drop; Create trigger trg1 BEFORE INSERT on t1 for each row set new.f1='Trigger 3.5.4.1'; connection con1_general; Use db_drop; Insert into t1 values ('Insert error 3.5.4.1'); Select * from t1 order by f1; connection con1_super; drop trigger trg1; select trigger_schema, trigger_name, event_object_table from information_schema.triggers order by trigger_name; connection con1_general; Insert into t1 values ('Insert no trigger 3.5.4.1'); Select * from t1 order by f1; #Cleanup --disable_warnings connection con1_super; --disable_warnings --error 0,ER_TRG_DOES_NOT_EXIST drop trigger trg1; drop database if exists db_drop; revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; --enable_warnings #Section 3.5.4.2 # Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error # message, if the trigger name does not exist. let $message= Testcase 3.5.4.2:; --source include/show_msg.inc connection con1_super; create database db_drop2; Use db_drop2; --disable_warnings drop table if exists t1_432 ; --enable_warnings eval create table t1_432 (f1 char (30)) engine=$engine_type; --error ER_TRG_DOES_NOT_EXIST Drop trigger tr_does_not_exit; #cleanup --disable_warnings drop table if exists t1_432 ; drop database if exists db_drop2; --enable_warnings #Section 3.5.4.3 # Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate # error message, if <trigger name> is not a qualified name. let $message= Testcase 3.5.4.3:; --source include/show_msg.inc connection con1_super; create database db_drop3; Use db_drop3; --disable_warnings drop table if exists t1_433 ; drop table if exists t1_433a ; --enable_warnings eval create table t1_433 (f1 char (30)) engine=$engine_type; eval create table t1_433a (f1a char (5)) engine=$engine_type; CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row set new.f1 = 'Trigger 3.5.4.3'; # Using table --error ER_PARSE_ERROR Drop trigger t1.433.trg3; # Using database.table --error ER_PARSE_ERROR Drop trigger db_drop3.t1.433.trg3; # wrong database --error ER_TRG_DOES_NOT_EXIST Drop trigger mysql.trg3; # database does not exist --error ER_TRG_DOES_NOT_EXIST Drop trigger tbx.trg3; #cleanup Drop trigger db_drop3.trg3; drop table if exists t1_433; drop table if exists t1_433a; drop database if exists db_drop3; #Section 3.5.4.4 # Test case: Ensure that when a database is dropped, all triggers created within # that database are also cleanly dropped. let $message= Testcase 3.5.4.4:; --source include/show_msg.inc connection con1_super; create database db_drop4; Use db_drop4; eval create table t1 (f1 char(30)) engine=$engine_type; grant INSERT, SELECT on db_drop4.t1 to test_general; Create trigger trg4 BEFORE INSERT on t1 for each row set new.f1='Trigger 3.5.4.4'; connection con1_general; Use db_drop4; Insert into t1 values ('Insert 3.5.4.4'); Select * from t1; connection con1_super; Drop database db_drop4; Show databases; select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg4'; create database db_drop4; Use db_drop4; eval create table t1 (f1 char(30)) engine=$engine_type; grant INSERT, SELECT on db_drop4.t1 to test_general; connection con1_general; Insert into t1 values ('2nd Insert 3.5.4.4'); Select * from t1; #Cleanup connection con1_super; --disable_warnings --error ER_TRG_DOES_NOT_EXIST drop trigger trg4; drop database if exists db_drop4; --enable_warnings revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; #Section 3.5.4.5 # Test case: Ensure that when a table is dropped, all triggers for which it is the # subject table are also cleanly dropped. let $message= Testcase 3.5.4.5:; --source include/show_msg.inc connection con1_super; create database db_drop5; Use db_drop5; eval create table t1 (f1 char(50)) engine=$engine_type; grant INSERT, SELECT on t1 to test_general; Create trigger trg5 BEFORE INSERT on t1 for each row set new.f1='Trigger 3.5.4.5'; connection con1_general; Use db_drop5; Insert into t1 values ('Insert 3.5.4.5'); Select * from t1; connection con1_super; Drop table t1; Show tables; select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg5'; eval create table t1 (f1 char(50)) engine=$engine_type; grant INSERT, SELECT on t1 to test_general; connection con1_general; Insert into t1 values ('2nd Insert 3.5.4.5'); Select * from t1; #Cleanup connection con1_super; --disable_warnings --error ER_TRG_DOES_NOT_EXIST drop trigger trg5; drop database if exists db_drop5; --enable_warnings revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; ################################## ######### Section 3.5.5 ########## # Checks on the Subject Table # ################################## let $message= Testcase 3.5.5:; --source include/show_msg.inc connection default; use test; #Section 3.5.5.1 # Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent # subject table, the statement fails with an appropriate error message. let $message= Testcase 3.5.5.1:; --source include/show_msg.inc --error ER_NO_SUCH_TABLE Create trigger trg1 before INSERT on t100 for each row set new.f2=1000; #Section 3.5.5.2 # Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table # as the subject table, the statement fails with an appropriate error message. let $message= Testcase 3.5.5.2:; --source include/show_msg.inc Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned); --error ER_TRG_ON_VIEW_OR_TEMP_TABLE Create trigger trg2 before INSERT on t1_temp for each row set new.f2=9999; #Cleanup --disable_warnings drop table t1_temp; --enable_warnings #Section 3.5.5.3 # Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject # table, the statement fails with an appropriate error message. let $message= Testcase 3.5.5.3:; --source include/show_msg.inc Create view vw3 as select f118 from tb3; # OBN Not sure why the server is returning error ER_WRONG_OBJECT --error ER_WRONG_OBJECT Create trigger trg3 before INSERT on vw3 for each row set new.f118='s'; #Cleanup --disable_warnings drop view vw3; --enable_warnings #Section 3.5.5.4 # Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides # in a different database than in which the trigger will reside, the # statement fails with an appropriate error message; that is, ensure that # the trigger and its subject table must reside in the same database. let $message= Testcase 3.5.5.4:; --source include/show_msg.inc connection con1_super; create database dbtest_one; create database dbtest_two; use dbtest_two; eval create table t2 (f1 char(15)) engine=$engine_type; use dbtest_one; --error ER_TRG_IN_WRONG_SCHEMA create trigger trg4 before INSERT on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4'; grant INSERT, SELECT on dbtest_two.t2 to test_general; grant SELECT on dbtest_one.* to test_general; connection con1_general; use dbtest_two; Insert into t2 values ('1st Insert 3.5.5.4'); Select * from t2; use dbtest_one; Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4'); Select * from dbtest_two.t2 order by f1; #Cleanup connection con1_super; --disable_warnings revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; DROP DATABASE if exists dbtest_one; drop database if EXISTS dbtest_two; --enable_warnings ##################################### ########### Section 3.5.6 ########### # Check on the Trigger Action Time # ##################################### let $message= Testcase 3.5.6:; --source include/show_msg.inc connection default; use test; #Section 3.5.6.1 # Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE. # See section 3.5.1.1 let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1); --source include/show_msg.inc #Section 3.5.6.2 # Test case: Ensure that a trigger definition can specify a trigger action time of AFTER. # See section 3.5.1.1 let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1); --source include/show_msg.inc #Section 3.5.6.3 # Test case: Ensure that a trigger definition that specifies a trigger action # time that is not either BEFORE or AFTER fails, with an appropriate # error message, at CREATE TRIGGER time. let $message= Testcase 3.5.6.3:; --source include/show_msg.inc --error ER_PARSE_ERROR Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25; --error ER_PARSE_ERROR Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15; #Cleanup # OBN - Although none of the above should have been created we should do a cleanup # since if they have been created, not dropping them will affect following # tests. --disable_warnings --error 0, ER_TRG_DOES_NOT_EXIST drop trigger tb3.trg3_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger tb3.trg3_2; --enable_warnings #Section 3.5.6.4 # Test case: Ensure that a trigger defined with a trigger action time of BEFORE # always executes its triggered action immediately before the trigger event. # See section 3.5.1.1 let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1); --source include/show_msg.inc #Section 3.5.6.5 # Test case: Ensure that a trigger defined with a trigger action time of AFTER # always executes its triggered action immediately after the trigger event. let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1); --source include/show_msg.inc ############################# ####### Section 3.5.7 ####### # Check on Trigger Event # ############################# #Section 3.5.7.1 #Test case: Ensure that a trigger definition can specify a trigger event of INSERT. let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1); --source include/show_msg.inc #Section 3.5.7.2 # Test case: Ensure that a trigger definition can specify a trigger event of UPDATE. let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1); --source include/show_msg.inc #Section 3.5.7.3 # Test case: Ensure that a trigger definition can specify a trigger event of DELETE. let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1); --source include/show_msg.inc #Section 3.5.7.4 # Test case: Ensure that a trigger definition that specifies a trigger event that # is not either INSERT, UPDATE or DELETE fails, with an appropriate error # message, at CREATE TRIGGER time. let $message= Testcase 3.5.7.4:; --source include/show_msg.inc --error ER_PARSE_ERROR Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5; --error ER_PARSE_ERROR Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1; #Cleanup # OBN - Although none of the above should have been created we should do a cleanup # since if they have been created, not dropping them will affect following # tests. --disable_warnings --error 0, ER_TRG_DOES_NOT_EXIST drop trigger tb3.trg4_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger tb3.trg4_2; --enable_warnings #Section 3.5.7.5 / 3.5.7.6 # Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers # on the same table, even if the triggers have different names / different # triggered actions. let $message= Testcase 3.5.7.5 / 3.5.7.6:; --source include/show_msg.inc Create trigger trg5_1 BEFORE INSERT on tb3 for each row set new.f122='Trigger1 3.5.7.5/6'; --error ER_NOT_SUPPORTED_YET Create trigger trg5_2 BEFORE INSERT on tb3 for each row set new.f122='Trigger2 3.5.7.5'; Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5'); Select f121,f122 from tb3 where f121='Test 3.5.7.5/6'; update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6'; Select f121,f122 from tb3 where f121='Test 3.5.7.5/6'; #Cleanup --disable_warnings drop trigger trg5_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger trg5_2; delete from tb3 where f121='Test 3.5.7.5/6'; --enable_warnings #Section 3.5.7.7 / 3.5.7.8 # Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers # on the same table, even if the triggers have different names / different # triggered actions. let $message= Testcase 3.5.7.7 / 3.5.7.8:; --source include/show_msg.inc set @test_var='Before trig 3.5.7.7'; Create trigger trg6_1 AFTER INSERT on tb3 for each row set @test_var='Trigger1 3.5.7.7/8'; --error ER_NOT_SUPPORTED_YET Create trigger trg6_2 AFTER INSERT on tb3 for each row set @test_var='Trigger2 3.5.7.7'; select @test_var; Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7'); Select f121,f122 from tb3 where f121='Test 3.5.7.7/8'; select @test_var; update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8'; Select f121,f122 from tb3 where f121='Test 3.5.7.7/8'; select @test_var; #Cleanup --disable_warnings drop trigger trg6_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger trg6_2; delete from tb3 where f121='Test 3.5.7.7/8'; --enable_warnings #Section 3.5.7.9 / 3.5.7.10 # Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers # on the same table, even if the triggers have different names / different # triggered actions. let $message= Testcase 3.5.7.9/10:; --source include/show_msg.inc Create trigger trg7_1 BEFORE UPDATE on tb3 for each row set new.f122='Trigger1 3.5.7.9/10'; --error ER_NOT_SUPPORTED_YET Create trigger trg7_2 BEFORE UPDATE on tb3 for each row set new.f122='Trigger2 3.5.7.9'; Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9'); Select f121,f122 from tb3 where f121='Test 3.5.7.9/10'; update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10'; Select f121,f122 from tb3 where f121='Test 3.5.7.9/10'; #Cleanup --disable_warnings drop trigger trg7_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger trg7_2; delete from tb3 where f121='Test 3.5.7.9/10'; #Section 3.5.7.11 / 3.5.7.12 # Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers # on the same table, even if the triggers have different names / different # triggered actions. let $message= Testcase 3.5.7.11/12:; --source include/show_msg.inc set @test_var='Before trig 3.5.7.11'; Create trigger trg8_1 AFTER UPDATE on tb3 for each row set @test_var='Trigger 3.5.7.11/12'; --error ER_NOT_SUPPORTED_YET Create trigger trg8_2 AFTER UPDATE on tb3 for each row set @test_var='Trigger2 3.5.7.11'; select @test_var; Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12'); select @test_var; Select f121,f122 from tb3 where f121='Test 3.5.7.11/12'; update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12'; Select f121,f122 from tb3 where f121='Test 3.5.7.11/12'; select @test_var; delete from tb3 where f121='Test 3.5.7.11/12'; #Cleanup --disable_warnings drop trigger trg8_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger trg8_2; delete from tb3 where f121='Test 3.5.7.11/12'; #Section 3.5.7.13 / 3.5.7.14 # Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers # on the same table, even if the triggers have different names / different # triggered actions. let $message= Testcase 3.5.7.13/14:; --source include/show_msg.inc set @test_var=1; Create trigger trg9_1 BEFORE DELETE on tb3 for each row set @test_var=@test_var+1; --error ER_NOT_SUPPORTED_YET Create trigger trg9_2 BEFORE DELETE on tb3 for each row set @test_var=@test_var+10; select @test_var; Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13'); Select f121,f122 from tb3 where f121='Test 3.5.7.13/14'; select @test_var; delete from tb3 where f121='Test 3.5.7.13/14'; Select f121,f122 from tb3 where f121='Test 3.5.7.13/14'; select @test_var; delete from tb3 where f121='Test 3.5.7.13/14'; select @test_var; #Cleanup --disable_warnings drop trigger trg9_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger trg9_2; delete from tb3 where f121='Test 3.5.7.13/14'; #Section 3.5.7.15 / 3.5.7.16 # Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers # on the same table, even if the triggers have different names / different # triggered actions. let $message= Testcase 3.5.7.15/16:; --source include/show_msg.inc set @test_var=1; Create trigger trg_3_406010_1 AFTER DELETE on tb3 for each row set @test_var=@test_var+5; --error ER_NOT_SUPPORTED_YET Create trigger trg_3_406010_2 AFTER DELETE on tb3 for each row set @test_var=@test_var+50; --error ER_TRG_ALREADY_EXISTS Create trigger trg_3_406010_1 AFTER INSERT on tb3 for each row set @test_var=@test_var+1; select @test_var; Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16'); Select f121,f122 from tb3 where f121='Test 3.5.7.15/16'; select @test_var; delete from tb3 where f121='Test 3.5.7.15/16'; Select f121,f122 from tb3 where f121='Test 3.5.7.15/16'; select @test_var; delete from tb3 where f121='Test 3.5.7.15/16'; select @test_var; #Cleanup --disable_warnings drop trigger trg_3_406010_1; --error 0, ER_TRG_DOES_NOT_EXIST drop trigger trg_3_406010_2; delete from tb3 where f121='Test 3.5.7.15/16'; --enable_warnings #Section 3.5.7.17 # Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT, # a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE # trigger on the same table; that is, ensure that every persistent base # table may be the subject table for exactly six triggers let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1); --source include/show_msg.inc # Cleanup section 3.5 connection default; drop user test_general@localhost; drop user test_general; drop user test_super@localhost;