triggers_0102.inc

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

#======================================================================
#
# Trigger Tests
# (test case numbering refer to requirement document TP v1.1)
#======================================================================

USE test;
--source suite/funcs_1/include/tb3.inc

# OBM - ToDo
############
# 1. Performace
###############################################

--disable_abort_on_error

#####################################################
################# Section 3.5.1 #####################
# Syntax checks for CREATE TRIGGER and DROP TRIGGER #
#####################################################

#Section 3.5.1.1
# Testcase: Ensure that all clauses that should be supported are supported.
let $message= Testcase: 3.5.1.1:;
--source include/show_msg.inc
# OBN - This test case tests basic trigger definition and execution
#       of INSERT/UPDATE/DELETE actions and BEFORE/AFTER timings.
#       As such it covers the equirements in sections 3.5.6.1, 3.5.6.2,
#       3.5.6.4, 3.5.6.5, 3.5.7.1, 3.5.7.2, 3.5.7.3, 3.5.7.17 below.
#     - Note currently as a result of limitations with locking tables in
#       triggers, a specifc lockingof the tables is done.
#       Once fixed, the locking and alias referances should be removed

use test;
# Trigger Definition
	Create trigger trg1_1 BEFORE INSERT
		on tb3 for each row set @test_before = 2, new.f142 = @test_before;
	Create trigger trg1_2 AFTER INSERT
		on tb3 for each row set @test_after = 6;
	Create trigger trg1_4 BEFORE UPDATE
		on tb3 for each row set @test_before = 27,
					new.f142 = @test_before,
					new.f122 = 'Before Update Trigger';
	Create trigger trg1_3 AFTER UPDATE
		on tb3 for each row set @test_after = '15';
	Create trigger trg1_5 BEFORE DELETE on tb3 for each row
		select count(*) into @test_before from tb3 as tr_tb3
			where f121 = 'Test 3.5.1.1';
	Create trigger trg1_6 AFTER DELETE on tb3 for each row
		select count(*) into @test_after from tb3 as tr_tb3
			where f121 = 'Test 3.5.1.1';
# Trigger Execution Insert (before and after)
	set @test_before = 1;
	set @test_after = 5;
        select @test_before, @test_after;
	Insert into tb3 (f121, f122, f142, f144, f134)
		values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
        --sorted_result
	select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
        select @test_before, @test_after;

# Trigger Execution Update (before and after)
	set @test_before = 18;
	set @test_after = 8;
        select @test_before, @test_after;
	Update tb3 set  tb3.f122 = 'Update',
			tb3.f142 = @test_before,
			tb3.f144 = @test_after
		where tb3.f121 = 'Test 3.5.1.1';
        --sorted_result
	select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
        select @test_before, @test_after;

# Trigger Execution Delete (before and after)
	Insert into tb3 (f121, f122, f142, f144, f134)
		values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
	set @test_before = 0;
	set @test_after = 0;
        --sorted_result
	select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
        select @test_before, @test_after;
	Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
        --sorted_result
	select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
        select @test_before, @test_after;

#Cleanup
	--disable_warnings
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg1_1;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg1_2;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg1_3;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg1_4;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg1_5;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg1_6;
	--enable_warnings
	delete from tb3 where f121='Test 3.5.1.1';
	--enable_warnings

#Section 3.5.1.2
# Testcase: Ensure that all clauses that should not be supported are disallowed
#           with an appropriate error message.
let $message= Testcase: 3.5.1.2:;
--source include/show_msg.inc
	
	--error ER_PARSE_ERROR
	Create trigger trg_1 after insert
		on tb3 for each statement set @x= 1;

#Cleanup
	--disable_warnings
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg_1;
	--enable_warnings
	

#Section 3.5.1.3
# Testcase: Ensure that all supported clauses are supported only in the correct order.
let $message= Testcase 3.5.1.3:;
--source include/show_msg.inc
	--error ER_PARSE_ERROR
	CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';

	--error ER_PARSE_ERROR
	CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
		
	--error ER_PARSE_ERROR
	CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
	
	--error ER_PARSE_ERROR
	CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;

	--error ER_PARSE_ERROR
	CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
	
#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 trg3_1;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg3_2;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg3_3;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg3_4;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg3_5;
	--enable_warnings


#Section 3.5.1.4
# Testcase: Ensure that an appropriate error message is returned if a clause
#           is out-of-order in an SQL statement.
# OBN - FIXME - Missing 3.5.1.4 need to add

#Section 3.5.1.5
# Testcase: Ensure that all clauses that are defined to be mandatory are indeed
#           required to be mandatory by the MySQL server and tools
let  $message= Testcase: 3.5.1.5:;
--source include/show_msg.inc

	--error ER_PARSE_ERROR
	CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';

	--error ER_PARSE_ERROR
	CREATE TRIGGER trg4_2 INSERT on tb3 for each set row  new.f120 = 'f';

	--error ER_PARSE_ERROR
	CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';

	--error ER_PARSE_ERROR
	CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';

	--error ER_PARSE_ERROR
	CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';

	--error ER_PARSE_ERROR
	CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';

#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 trg4_1;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg4_2;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg4_3;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg4_4;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg4_5;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg4_6;
	--enable_warnings

#Section 3.5.1.6
# Testcase: Ensure that any clauses that are defined to be optional are indeed
#           trated as optional by MySQL server and tools
let $message= Testcase 3.5.1.6: - Need to fix;
--source include/show_msg.inc
# OBN - FIXME - Missing 3.5.1.6 need to add

#Section 3.5.1.7
# Testcase: Ensure that all valid, fully-qualified, and non-qualified,
#           trigger names are accepted, at creation time.
let $message= Testcase 3.5.1.7: - need to fix;
--source include/show_msg.inc

	drop table if exists t1;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t1 (f1 int, f2 char(25),f3 int) engine = $engine_type;
	CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
		for each row set new.f3 = '14';
	CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
		BEFORE UPDATE on test.t1 for each row set new.f3 = '42';

	insert into t1 (f2) values ('insert 3.5.1.7');
	select * from t1;
	update t1 set f2='update 3.5.1.7';
	select * from t1;
	select trigger_name from information_schema.triggers order by trigger_name;