triggers_0102.inc

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

#Cleanup
	--disable_warnings
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg5_1;
	# The above trigger should be dropped since the name was trimmed.
	drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
	drop table t1;

#Section 3.5.1.8
# Testcase: Ensure that any invalid trigger name is never accepted, and that an
#            appropriate error message is returned when the name is rejected.
let $message= Testcase 3.5.1.8:;
--source include/show_msg.inc

	--error ER_PARSE_ERROR
	CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';

	--error ER_PARSE_ERROR
	CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';

	--error ER_PARSE_ERROR
	CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';

	--error ER_PARSE_ERROR
	CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';

	--error ER_PARSE_ERROR
	CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';

	--error ER_TRG_IN_WRONG_SCHEMA
	CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
		for each row set new.f120 ='X';
	
	--disable_warnings
        drop database if exists trig_db;
	--enable_warnings
	create database trig_db;
	use trig_db;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t1 (f1 integer) engine = $engine_type;

	# Can't create a trigger in a different database
	use test;
	--error ER_NO_SUCH_TABLE
	CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
		for each row set @ret_trg6_2 = 5;

	# Can't create a trigger refrencing a table in a different db
	use trig_db;
	--error ER_TRG_IN_WRONG_SCHEMA
	CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
		for each row set @ret_trg6_3 = 18;

	use test;

#Cleanup
	--disable_warnings
	drop database trig_db;
# 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.
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg6_1;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trg6_3;
	--enable_warnings

#Section 3.5.1.9
#Testcase:  Ensure that a reference to a non-existent trigger is rejected with
#           an appropriate error message.
let $message= Testcase 3.5.1.9:(cannot be inplemented at this point);
--source include/show_msg.inc


#Section 3.5.1.10
#Testcase: Ensure that it is not possible to create two triggers with the same name on
#          the same table
let $message= Testcase 3.5.1.10:;
--source include/show_msg.inc

	CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';

	--error ER_TRG_ALREADY_EXISTS
	CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';

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


#Section 3.5.1.?
# Testcase: Ensure that it is not possible to create two or more triggers with
#           the same name, provided each is associated with a different table.
let $message= Testcase 3.5.1.?:;
--source include/show_msg.inc

	--disable_warnings
	drop table if exists t1;
	drop table if exists t2;
	--enable_warnings
        --replace_result $engine_type <engine_to_be_used>
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t2 (f1 char(50), f2 integer) engine = $engine_type;

	create trigger trig before insert on t1
		for each row set new.f1 ='trig t1';

	--error ER_TRG_ALREADY_EXISTS
	create trigger trig before update on t2
		for each row set new.f1 ='trig t2';

	insert into t1 value ('insert to t1',1);
	select * from t1;
	update t1 set f1='update to t1';
	select * from t1;
	insert into t2 value ('insert to t2',2);
	update t2 set f1='update to t1';
	select * from t2;

#Cleanup
	--disable_warnings
	drop table t1;
	drop table t2;
	--error 0, ER_TRG_DOES_NOT_EXIST
	drop trigger trig;
	--enable_warnings


#Section 3.5.1.11
# Testcase: Ensure that it is possible to create two or more triggers with
#           the same name, provided each resides in a different database
let $message= Testcase 3.5.1.11:;
--source include/show_msg.inc

	--disable_warnings
        drop database if exists trig_db1;
        drop database if exists trig_db2;
        drop database if exists trig_db3;
	--enable_warnings
	create database trig_db1;
	create database trig_db2;
	create database trig_db3;
	use trig_db1;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
	create trigger trig before insert on t1
		for each row set new.f1 ='trig1', @test_var1='trig1';
	use trig_db2;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t2 (f1 char(50), f2 integer) engine = $engine_type;
	create trigger trig before insert on t2
		for each row set new.f1 ='trig2', @test_var2='trig2';
	use trig_db3;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
	create trigger trig before insert on t1
		for each row set new.f1 ='trig3', @test_var3='trig3';

	set @test_var1= '', @test_var2= '', @test_var3= '';
	use trig_db1;
	insert into t1 (f1,f2) values ('insert to db1 t1',1);
	insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
	insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
	insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
	select @test_var1, @test_var2, @test_var3;
	select * from t1 order by f2;
	select * from trig_db2.t2;
	select * from trig_db3.t1;
	select * from t1 order by f2;
	use test;

#Cleanup
	--disable_warnings
	drop database trig_db1;
	drop database trig_db2;
	drop database trig_db3;
	--enable_warnings

###########################################
################ Section 3.5.2 ############
# Check for the global nature of Triggers #
###########################################

#Section 3.5.2.1
# Test case: Ensure that if a trigger created without a qualifying database
#            name belongs to the database in use at creation time.
#Section 3.5.2.2
# Test case: Ensure that if a trigger created with a qualifying database name
#            belongs to the database specified.
#Section 3.5.2.3
# Test case: Ensure that if a trigger created with a qualifying database name
#            does not belong to the database in use at creation time unless
#            the qualifying database name identifies the database that is
#            also in use at creation time.
let $message= Testcase 3.5.2.1/2/3:;
--source include/show_msg.inc


	--disable_warnings
        drop database if exists trig_db1;
        drop database if exists trig_db2;
	--enable_warnings
	create database trig_db1;
	create database trig_db2;
	use trig_db1;
        --replace_result $engine_type <engine_to_be_used>
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
        --replace_result $engine_type <engine_to_be_used>
	eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type;
	create trigger trig1_b before insert on t1
		for each row set @test_var1='trig1_b';
	create trigger trig_db1.trig1_a after insert on t1
		for each row set @test_var2='trig1_a';
	create trigger trig_db2.trig2 before insert on trig_db2.t1
		for each row set @test_var3='trig2';
	select trigger_schema, trigger_name, event_object_table
		from information_schema.triggers order by trigger_name;

	set @test_var1= '', @test_var2= '', @test_var3= '';
	insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
	insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
	select @test_var1, @test_var2, @test_var3;

#Cleanup
	--disable_warnings
	drop database trig_db1;
	drop database trig_db2;
DROP TABLE test.tb3;