triggers_0407.inc

来自「这个文件是windows mysql源码」· INC 代码 · 共 625 行 · 第 1/2 页

	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;

DROP TABLE test.tb3;