~drizzle-trunk/drizzle/development : contents of mysql-test/suite/funcs_1/triggers/triggers

~drizzle-trunk/drizzle/development : (revision 33)
#======================================================================
#
# Trigger Tests 
# (test case numbering refer to requirement document TP v1.1) 
#======================================================================

# OBM - ToDo
############
# 1. Information Schema Trigger Table
# 2. Performace
###############################################

# 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 (con_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (con_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;


#####################################################
################# 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);
	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';
	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;
	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;
	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, 1360
	drop trigger trg1_1;
	--error 0, 1360
	drop trigger trg1_2;
	--error 0, 1360
	drop trigger trg1_3;
	--error 0, 1360
	drop trigger trg1_4;
	--error 0, 1360
	drop trigger trg1_5;
	--error 0, 1360
	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 1064
	Create trigger trg_1 after insert 
		on tb3 for each statement set @x= 1;

#Cleanup 
	--disable_warnings 
	--error 0, 1360
	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 1064 
	CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';

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

	--error 1064 
	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, 1360
	drop trigger trg3_1;
	--error 0, 1360
	drop trigger trg3_2;
	--error 0, 1360
	drop trigger trg3_3;
	--error 0, 1360
	drop trigger trg3_4;
	--error 0, 1360
	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 1064 
	CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';

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

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

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

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

	--error 1064 
	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, 1360
	drop trigger trg4_1;
	--error 0, 1360
	drop trigger trg4_2;
	--error 0, 1360
	drop trigger trg4_3;
	--error 0, 1360
	drop trigger trg4_4;
	--error 0, 1360
	drop trigger trg4_5;
	--error 0, 1360
	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;
	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;

#Cleanup
	--disable_warnings 
	--error 0, 1360
	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 1064 
	CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';

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

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

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

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

	--error 1435	
	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;
	eval create table t1 (f1 integer) engine = $engine_type;

	# Can't create a trigger in a different database
	use test;
	--error 1435
	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 1435
	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, 1360
	drop trigger trg6_1;
	--error 0, 1360
	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 1359
	CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';

#Cleanup
	--disable_warnings 
	--error 0, 1360
	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
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
	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 1359
	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, 1360
	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;
	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;
	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;
	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;
	select * from trig_db2.t2;
	select * from trig_db3.t1;
	select * from t1;
	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;
	eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
	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;

	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;

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

# General setup to be used in all testcases of 3.5.3
let $message= Testcase 3.5.3:;
--source include/show_msg.inc

        --disable_warnings
	drop database if exists priv_db;
	--enable_warnings
	create database priv_db;
	use priv_db;
	create table t1 (f1 char(20));

	create User test_noprivs@localhost;
	set password for test_noprivs@localhost = password('PWD');

	create User test_yesprivs@localhost;
	set password for test_yesprivs@localhost = password('PWD');

#Section 3.5.3.1 / 3.5.3.2 
# Test case: Ensure SUPER privilege is required to create a trigger
#Section 3.5.3.3 / 3.5.3.4
# Test case: Ensure that root always has the SUPER privilege.
# OMR - No need to test this since SUPER priv is an existing one and not related
#       or added for triggers (TP 2005-06-06) 
#Section 3.5.3.5 / 3.5.3.6 
# Test case: Ensure that the SUPER privilege is required to drop a trigger.
let $message= Testcase 3.5.3.2/6:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant ALL  on *.* to test_noprivs@localhost;
	revoke SUPER  on *.* from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
# Adding the minimal priv to be able to set to the db
	grant SELECT on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

let $message= Testcase 3.5.3.2:;
--source include/show_msg.inc
	
	connection no_privs;
        select current_user;
	use priv_db;
	
	--error 1227
	create trigger trg1_1 before INSERT on t1 for each row 
		set new.f1 = 'trig 3.5.3.2_1-no';

	connection default;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.2-no');
	select f1 from t1;

	connection yes_privs;
        select current_user;
	use priv_db;

	create trigger trg1_2 before INSERT  on t1 for each row 
		set new.f1 = 'trig 3.5.3.2_2-yes';

	connection default;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.2-yes');
	select f1 from t1;
	
let $message= Testcase 3.5.3.6:;
--source include/show_msg.inc

	connection no_privs;
	use priv_db;
	
	--error 1227
	drop trigger trg1_2;  

	connection default;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.6-yes');
	select f1 from t1;

	connection yes_privs;
	use priv_db;

	drop trigger trg1_2;  

	connection default;
	use priv_db;
	insert into t1 (f1) values ('insert 3.5.3.6-no');
	select f1 from t1;

# Cleanup	
	--disable_warnings 
	connection default;
	--error 0, 1360
	drop trigger trg1_2;
	disconnect no_privs;
	disconnect yes_privs;
	--enable_warnings


#Section 3.5.3.7
# Test case: Ensure that use of the construct "SET NEW. <column name> = <value>" 
#            fails at CREATE TRIGGER time, if the current user does not have the
#            UPDATE privilege on the column specified
# Note: As a result of bug 8884 the triggers are actually created. 
#       Disabled because of bug 8884

# --- 3.5.3.7a - Privs set on a global level
let $message=Testcase 3.5.3.7a:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant ALL  on *.* to test_noprivs@localhost;
	revoke UPDATE  on *.* from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER, UPDATE on *.* to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);

	connection no_privs_424a;
	select current_user;
	use priv_db;
	show grants;
	select f1 from t1;

let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg4a_1 before INSERT on t1 for each row 
#		set new.f1 = 'trig 3.5.3.7-1a';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-1a');
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4a_1;

	connection yes_privs_424a;
	use priv_db;
	select current_user;
	show grants;
	create trigger trg4a_2 before INSERT  on t1 for each row 
		set new.f1 = 'trig 3.5.3.7-2a';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-2b');
	select f1 from t1;

# Cleanup
	--disable_warnings 
	drop trigger trg4a_2;
	disconnect no_privs_424a;
	disconnect yes_privs_424a;
	--enable_warnings

# --- 3.5.3.7b - Privs set on a database level
let $message= Testcase 3.5.3.7b:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs;
	grant ALL  on priv_db.* to test_noprivs@localhost;
	revoke UPDATE  on priv_db.* from test_noprivs@localhost;
	show grants for test_noprivs;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant UPDATE on priv_db.* to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_424b;
	show grants;
	use priv_db;

let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg4b_1 before UPDATE on t1 for each row 
#		set new.f1 = 'trig 3.5.3.7-1b';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-1b');
	select f1 from t1;
	update t1 set  f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4b_1;

	connection yes_privs_424b;
	show grants;
	use priv_db;
	create trigger trg4b_2 before UPDATE  on t1 for each row 
		set new.f1 = 'trig 3.5.3.7-2b';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-2b');
	select f1 from t1;
	update t1 set  f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
	select f1 from t1;
# Cleanup
	--disable_warnings 
	drop trigger trg4b_2;
	disconnect no_privs_424b;
	disconnect yes_privs_424b;
	--enable_warnings

# --- 3.5.3.7c - Privs set on a table level
let $message= Testcase 3.5.3.7c;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
	grant ALL  on priv_db.t1 to test_noprivs@localhost;
	revoke UPDATE  on priv_db.t1 from test_noprivs@localhost;
	show grants for test_noprivs;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_424c;
	show grants;
	use priv_db;

let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg4c_1 before INSERT on t1 for each row 
#		set new.f1 = 'trig 3.5.3.7-1c';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-1c');
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4c_1;

	connection yes_privs_424c;
	show grants;
	use priv_db;
	create trigger trg4c_2 before INSERT  on t1 for each row 
		set new.f1 = 'trig 3.5.3.7-2c';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-2c');
	select f1 from t1;

# Cleanup
	--disable_warnings 
	drop trigger trg4c_2;
	disconnect no_privs_424c;
	disconnect yes_privs_424c;
	--enable_warnings

# --- 3.5.3.7d - Privs set on a column level
--disable_query_log
let $message= Testcase 3.5.3.7d:;
--enable_query_log
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
# There is no ALL privs on the column level
	grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
	show grants for test_noprivs;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_noprivs;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_424d;
	show grants;
	use priv_db;
let $message= Trigger create disabled - should fail - Bug 8884;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg4d_1 before INSERT on t1 for each row 
#		set new.f1 = 'trig 3.5.3.7-1d';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-1d');
	select f1 from t1;
	--error 0, 1360
	drop trigger trg4d_1;

	connection yes_privs_424d;
	show grants;
	use priv_db;
	create trigger trg4d_2 before INSERT  on t1 for each row 
		set new.f1 = 'trig 3.5.3.7-2d';

	connection default;
	insert into t1 (f1) values ('insert 3.5.3.7-2d');
	select f1 from t1;

# Cleanup
	--disable_warnings 
	drop trigger trg4d_2;
	disconnect no_privs_424d;
	disconnect yes_privs_424d;
	--enable_warnings

#Section 3.5.3.8
# Test case: Ensure that use of the construct "SET <target> = NEW. <Column name>" fails 
#            at CREATE TRIGGER time, if the current user does not have the SELECT privilege 
#            on the column specified.

# --- 3.5.3.8a - Privs set on a global level
let $message= Testcase 3.5.3.8a:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant ALL  on *.* to test_noprivs@localhost;
	revoke SELECT  on *.* from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER, SELECT on *.* to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425a;
	select current_user;
	use priv_db;
	show grants;

let $message= Trigger create disabled - should fail - Bug 8887;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg5a_1 before INSERT on t1 for each row 
#		set @test_var = new.f1;

	connection default;
        set @test_var = 'before trig 3.5.3.8-1a';
	select @test_var;
	insert into t1 (f1) values ('insert 3.5.3.8-1a');
	select @test_var;
	--error 0, 1360
	drop trigger trg5a_1;

	connection yes_privs_425a;
	use priv_db;
	select current_user;
	show grants;
	create trigger trg5a_2 before INSERT  on t1 for each row 
		set @test_var= new.f1;

	connection default;
        set @test_var= 'before trig 3.5.3.8-2a';
	select @test_var;
	insert into t1 (f1) values ('insert 3.5.3.8-2a');
	select @test_var;

# Cleanup
	--disable_warnings 
	drop trigger trg5a_2;
	disconnect no_privs_425a;
	disconnect yes_privs_425a;
	--enable_warnings

# --- 3.5.3.8b - Privs set on a database level
let $message= Testcase: 3.5.3.8b;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
	grant ALL  on priv_db.* to test_noprivs@localhost;
	revoke SELECT  on priv_db.* from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT on priv_db.* to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425b;
	show grants;
	use priv_db;

let $message= Trigger create disabled - should fail - Bug 8887;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg5b_1 before UPDATE on t1 for each row 
#		set @test_var= new.f1;

	connection default;
        set @test_var= 'before trig 3.5.3.8-1b';
	insert into t1 (f1) values ('insert 3.5.3.8-1b');
	select @test_var;
	update t1 set  f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
	select @test_var;
	--error 0, 1360
	drop trigger trg5b_1;

	connection yes_privs_425b;
	show grants;
	use priv_db;
	create trigger trg5b_2 before UPDATE  on t1 for each row 
		set @test_var= new.f1;

	connection default;
        set @test_var= 'before trig 3.5.3.8-2b';
	insert into t1 (f1) values ('insert 3.5.3.8-2b');
	select @test_var;
	update t1 set  f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
	select @test_var;
# Cleanup
	--disable_warnings 
	drop trigger trg5b_2;
	disconnect no_privs_425b;
	disconnect yes_privs_425b;
	--enable_warnings

# --- 3.5.3.8c - Privs set on a table level
let $message= Testcase 3.5.3.8c:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
	grant ALL  on priv_db.t1 to test_noprivs@localhost;
	revoke SELECT  on priv_db.t1 from test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_yesprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425c;
	show grants;
	use priv_db;

let $message= Trigger create disabled - should fail - Bug 8887;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg5c_1 before INSERT on t1 for each row 
#		set @test_var= new.f1;

	connection default;
        set @test_var= 'before trig 3.5.3.8-1c';
	insert into t1 (f1) values ('insert 3.5.3.8-1c');
	select @test_var;
	--error 0, 1360
	drop trigger trg5c_1;

	connection yes_privs_425c;
	show grants;
	use priv_db;
	create trigger trg5c_2 before INSERT  on t1 for each row 
		set @test_var= new.f1;

	connection default;
        set @test_var='before trig 3.5.3.8-2c';
	insert into t1 (f1) values ('insert 3.5.3.8-2c');
	select @test_var;
# Cleanup
	--disable_warnings 
	drop trigger trg5c_2;
	disconnect no_privs_425c;
	disconnect yes_privs_425c;
	--enable_warnings

# --- 3.5.3.8d - Privs set on a column level
let $message=Testcase: 3.5.3.8d:;
--source include/show_msg.inc

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
	grant SUPER on *.* to test_noprivs@localhost;
# There is no ALL prov on the column level
	grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
	show grants for test_noprivs@localhost;

	revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
	grant SUPER on *.* to test_yesprivs@localhost;
	grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
	show grants for test_noprivs@localhost;

	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

	connection no_privs_425d;
	show grants;
	use priv_db;
let $message= Trigger create disabled - should fail - Bug 8887;
--source include/show_msg.inc
#	--error 1227
#	create trigger trg5d_1 before INSERT on t1 for each row 
#		set @test_var= new.f1;

	connection default;
        set @test_var='before trig 3.5.3.8-1d';
	insert into t1 (f1) values ('insert 3.5.3.8-1d');
	select @test_var;
	--error 0, 1360
	drop trigger trg5d_1;

	connection yes_privs_425d;
	show grants;
	use priv_db;
	create trigger trg5d_2 before INSERT  on t1 for each row 
		set @test_var= new.f1;

	connection default;
        set @test_var='before trig 3.5.3.8-2d';
	insert into t1 (f1) values ('insert 3.5.3.8-2d');
	select @test_var;

# Cleanup 3.5.3.8
	--disable_warnings 
	drop trigger trg5d_2;
	--enable_warnings

# Cleanup 3.5.3
	--disable_warnings 
	drop database if exists priv_db;
	drop user test_yesprivs@localhost;
	drop user test_noprivs@localhost;
	drop user test_noprivs;
	--enable_warnings

####################################
############ 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 con_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 con_general;
	Use db_drop;
	Insert into t1 values ('Insert error 3.5.4.1');
	Select * from t1;
	connection con_super;
	drop trigger trg1;
	select trigger_schema, trigger_name, event_object_table
		from information_schema.triggers;
	connection con_general;
	Insert into t1 values ('Insert no trigger 3.5.4.1');
	Select * from t1;

#Cleanup
	--disable_warnings 
	connection con_super;
        --disable_warnings
	--error 0,1360
	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 con_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 1360
	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 con_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 1064
	Drop trigger t1.433.trg3;

# Using database.table 
	--error 1064
	Drop trigger db_drop3.t1.433.trg3;

# wrong database
	--error 1360
	Drop trigger mysql.trg3;

# database does not exist
	--error 1360
	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 con_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 con_general;
	Use db_drop4;
	Insert into t1 values ('Insert 3.5.4.4');
	Select * from t1;
	connection con_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 con_general;
	Insert into t1 values ('2nd Insert 3.5.4.4');
	Select * from t1;

#Cleanup
	connection con_super;
        --disable_warnings
	--error 1360
	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 con_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 con_general;
	Use db_drop5;
	Insert into t1 values ('Insert 3.5.4.5');
	Select * from t1;
	connection con_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 con_general;
	Insert into t1 values ('2nd Insert 3.5.4.5');
	Select * from t1;

#Cleanup
	connection con_super;
        --disable_warnings
	--error 1360
	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 1146
	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 1361
	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 1347
	--error 1347
	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 con_super;
	create database dbtest_one;
	create database dbtest_two;
	use dbtest_two;
	create table t2 (f1 char(15));
	use dbtest_one;
	--error 1435
	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 con_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;

#Cleanup
	connection con_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 1064
	Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
	--error 1064
	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, 1360
	drop trigger tb3.trg3_1;
	--error 0, 1360
	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 1064
	Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
	--error 1064
	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, 1360
	drop trigger tb3.trg4_1;
	--error 0, 1360
	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 1359 
	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, 1360
	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 1359 
	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, 1360
	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 1359 
	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, 1360
	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 1359 
	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, 1360
	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 1359 
	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, 1360
	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 1359 
	Create trigger trg_3_406010_2 AFTER DELETE 
		on tb3 for each row set @test_var=@test_var+50;

	--error 1359 
	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, 1360
	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


#################################
####### Section 3.5.8 ###########
# Checks on Triggered Actions   #
#################################

#Section 3.5.8.1
# Testcase: Ensure that the triggered action of every trigger always executes 
#           correctly and the results in all expected changes made to the database
let $message= Testcase 3.5.8.1: (implied in previous tests);
--source include/show_msg.inc
# OBN - FIXME - Missing 3.5.8.1 need to add

#Section 3.5.8.2
# Testcase: Ensure that the triggered actions of every trigger never results 
#           in an unexpected change made to the database.
let $message= Testcase 3.5.8.2: (implied in previous tests);
--source include/show_msg.inc


#Section 3.5.8.3 / 3.5.8.4
#Test case: Ensure that the triggered action can any valid SQL statement / set 
#           of valid SQL statements, provided the statements are written within
#           a BEGIN/END compound statement construct
# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
#       as there are the most likely to be used in triggers
let $message= Testcase 3.5.8.3/4:;
--source include/show_msg.inc

# creating test tables to perform the trigger SQL on
	connection con_super;
	create database db_test;
	grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
	grant LOCK TABLES on db_test.* to test_general;
	Use db_test;
	eval create table t1_i ( 
		i120 char ascii not null DEFAULT b'101',
		i136 smallint zerofill not null DEFAULT 999,
		i144 int zerofill not null DEFAULT 99999,
		i163 decimal (63,30)) engine=$engine_type;
	eval create table t1_u ( 
		u120 char ascii not null DEFAULT b'101',
		u136 smallint zerofill not null DEFAULT 999,
		u144 int zerofill not null DEFAULT 99999,
		u163 decimal (63,30)) engine=$engine_type;
	eval create table t1_d ( 
		d120 char ascii not null DEFAULT b'101',
		d136 smallint zerofill not null DEFAULT 999,
		d144 int zerofill not null DEFAULT 99999,
		d163 decimal (63,30)) engine=$engine_type;
	Insert into t1_u values ('a',111,99999,999.99);
	Insert into t1_u values ('b',222,99999,999.99);
	Insert into t1_u values ('c',333,99999,999.99);
	Insert into t1_u values ('d',222,99999,999.99);
	Insert into t1_u values ('e',222,99999,999.99);
	Insert into t1_u values ('f',333,99999,999.99);
	Insert into t1_d values ('a',111,99999,999.99);
	Insert into t1_d values ('b',222,99999,999.99);
	Insert into t1_d values ('c',333,99999,999.99);
	Insert into t1_d values ('d',444,99999,999.99);
	Insert into t1_d values ('e',222,99999,999.99);
	Insert into t1_d values ('f',222,99999,999.99);

let $message= 3.5.8.4 - multiple SQL;
--source include/show_msg.inc
# Trigger definition - multiple SQL
	use test;
	delimiter //;
	Create trigger trg1 AFTER INSERT on tb3 for each row
	BEGIN
		insert into db_test.t1_i 
			values (new.f120, new.f136, new.f144, new.f163);
		update db_test.t1_u 
			set u144=new.f144, u163=new.f163
			where u136=new.f136; 
		delete from db_test.t1_d where d136= new.f136;
		select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u 
			where u136= new.f136; 
	END// 
	delimiter ;//

# Test trigger execution - multiple SQL
	connection con_general;
	Use test;
	set @test_var=0;
	Insert into tb3 (f120, f122, f136, f144, f163) 
		values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
	Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
	select * from db_test.t1_i;
	select * from db_test.t1_u;
	select * from db_test.t1_d;
	select @test_var;


let $message= 3.5.8.4 - single SQL - insert;
--source include/show_msg.inc
# Trigger definition - single SQL Insert
	connection con_super;
	Create trigger trg2 BEFORE UPDATE on tb3 for each row
		insert into db_test.t1_i 
			values (new.f120, new.f136, new.f144, new.f163);

# Trigger exeution - single SQL Insert
	connection con_general;
	update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
		 where f122='Test 3.5.8.4';
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select * from db_test.t1_i;


let $message= 3.5.8.4 - single SQL - update;
--source include/show_msg.inc
# Trigger definition - single SQL update
	connection con_super;
	drop trigger trg2;
	Create trigger trg3 BEFORE UPDATE on tb3 for each row
		update db_test.t1_u 
			set u120=new.f120
			where u136=new.f136; 

# Trigger exeution - single SQL  - update;
	connection con_general;
	update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
		 where f122='Test 3.5.8.4-Single Insert';
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select * from db_test.t1_u;


let $message= 3.5.8.3/4 - single SQL - delete;
--source include/show_msg.inc
# Trigger definition - single SQL delete
	connection con_super;
	drop trigger trg3;
	Create trigger trg4 AFTER UPDATE on tb3 for each row
		delete from db_test.t1_d where d136= new.f136;

# Trigger exeution - single SQL delete
	connection con_general;
#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
	update tb3 set f120='D', f136=444, 
		f122='Test 3.5.8.4-Single Delete'
		 where f122='Test 3.5.8.4-Single Update';
#unlock tables;
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select * from db_test.t1_d;


let $message= 3.5.8.3/4 - single SQL - select;
--source include/show_msg.inc
# Trigger definition - single SQL select
	connection con_super;
	drop trigger trg4;
	Create trigger trg5 AFTER UPDATE on tb3 for each row
		select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u 
			where u136= new.f136; 

# Trigger exeution - single SQL select
	connection con_general;
	set @test_var=0;
	update tb3 set f120='S', f136=111, 
		f122='Test 3.5.8.4-Single Select'
		 where f122='Test 3.5.8.4-Single Delete';
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select @test_var;

#Cleanup
	connection default;
	--disable_warnings 
	drop trigger trg1;
	drop trigger trg5;
	drop database if exists db_test;
	delete from tb3 where f122 like 'Test 3.5.8.4%';
	revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
	--enable_warnings


#Section 3.5.8.5 (IF)
# Test case: Ensure that the stored procedure-specific flow control statement like IF 
#            works correctly when it is a part of the triggered action portion of a 
#            trigger definition.
let $message= Testcase 3.5.8.5 (IF):;
--source include/show_msg.inc

	delimiter //;
	create trigger trg2 before insert on tb3 for each row
	BEGIN
		IF new.f120='1' then
			set @test_var='one', new.f120='2';
		ELSEIF new.f120='2' then
			set @test_var='two', new.f120='3';
		ELSEIF new.f120='3' then
			set @test_var='three', new.f120='4';
		END IF;

		IF (new.f120='4') and (new.f136=10) then
			set @test_var2='2nd if', new.f120='d';
		ELSE 
			set @test_var2='2nd else', new.f120='D';
		END IF;
	END//
	delimiter ;//

	set @test_var='Empty', @test_var2=0;
	Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if';
	Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if';
	Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if';
	Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if';

	delimiter //;
	--error 1064
	create trigger trg3 before update on tb3 for each row
	BEGIN
		ELSEIF new.f120='2' then
		END IF;
	END//
	--error 0, 1360
	drop trigger trg3//

	--error 1064
	create trigger trg4 before update on tb3 for each row
	BEGIN
		IF (new.f120='4') and (new.f136=10) then
			set @test_var2='2nd if', new.f120='d';
		ELSE 
			set @test_var2='2nd else', new.f120='D';
	END//
	delimiter ;//
	--error 0, 1360
	drop trigger trg4;

#Cleanup
	--disable_warnings 
	drop trigger trg2;
	delete from tb3 where f121='Test 3.5.8.5-if';	
	--enable_warnings


#Section 3.5.8.5 (CASE)
# Test case: Ensure that the stored procedure-specific flow control statement 
#            like CASE works correctly when it is a part of the triggered action 
#            portion of a trigger definition.
let $message= Testcase 3.5.8.5-case:;
--source include/show_msg.inc
	
	delimiter //;
	create trigger trg3 before insert on tb3 for each row
	BEGIN
		SET new.f120=char(ascii(new.f120)-32);
		CASE
			when new.f136<100 then set new.f136=new.f136+120;
			when new.f136<10 then set new.f144=777;
			when new.f136>100 then set new.f120=new.f136-1;
		END case;
		CASE
			when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
			ELSE set @test_var=concat(new.f120, '*');
		END case;
		CASE new.f144
			when 1 then set @test_var=concat(@test_var, 'one');
			when 2 then set @test_var=concat(@test_var, 'two');
			when 3 then set @test_var=concat(@test_var, 'three');
			when 4 then set @test_var=concat(@test_var, 'four');
			when 5 then set @test_var=concat(@test_var, 'five');
			when 6 then set @test_var=concat(@test_var, 'six');
			when 7 then set @test_var=concat(@test_var, 'seven');
			when 8 then set @test_var=concat(@test_var, 'eight');
			when 9 then set @test_var=concat(@test_var, 'nine');
			when 10 then set @test_var=concat(@test_var, 'ten');
			when 11 then set @test_var=concat(@test_var, 'eleven');
			when 12 then set @test_var=concat(@test_var, 'twelve');
			when 13 then set @test_var=concat(@test_var, 'thirteen');
			when 14 then set @test_var=concat(@test_var, 'fourteen');
			when 15 then set @test_var=concat(@test_var, 'fifteen');
			ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
		END case;
	END//
	delimiter ;//

	set @test_var='Empty';
	Insert into tb3 (f120, f122, f136, f144) 
		values ('a', 'Test 3.5.8.5-case', 5, 7);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case';
	Insert into tb3 (f120, f122, f136, f144) 
		values ('b', 'Test 3.5.8.5-case', 71,16);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case';
	Insert into tb3 (f120, f122, f136, f144) 
		values ('c', 'Test 3.5.8.5-case', 80,1);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case';
	Insert into tb3 (f120, f122, f136) 
		values ('d', 'Test 3.5.8.5-case', 152);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case';
	Insert into tb3 (f120, f122, f136, f144) 
		values ('e', 'Test 3.5.8.5-case', 200, 8);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case';
	--error 0, 1339
	Insert into tb3 (f120, f122, f136, f144) 
		values ('f', 'Test 3.5.8.5-case', 100, 8);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case';

	delimiter //;
	--error 1064
	create trigger trg3a before update on tb3 for each row
	BEGIN
		CASE
			when new.f136<100 then set new.f120='p';
	END//
	delimiter ;//

	--error 0, 1360
	drop trigger trg3a;

#Cleanup
	--disable_warnings 
	drop trigger trg3;
	delete from tb3 where f121='Test 3.5.8.5-case';	
	--enable_warnings

#Section 3.5.8.5 (LOOP)
# Test case: Ensure that the stored procedure-specific flow control
#            statement like LOOP / LEAVE work correctly when they are
#            part of the triggered action portion of a trigger definition.
let $message= Testcase 3.5.8.5-loop/leave:;
--source include/show_msg.inc
		
	delimiter //;
	Create trigger trg4 after insert on tb3 for each row
	BEGIN 
		set @counter=0, @flag='Initial';
		Label1: loop 
			if new.f136<new.f144 then
				set @counter='Nothing to loop';
				leave Label1; 
			else
				set @counter=@counter+1;
				if new.f136=new.f144+@counter then
					set @counter=concat(@counter, ' loops');
					leave Label1;
				end if; 
			end if; 
			iterate label1; 
			set @flag='Final';
		END loop Label1; 
	END//
	delimiter ;//
	Insert into tb3 (f122, f136, f144) 
		values ('Test 3.5.8.5-loop', 2, 8);
		select @counter, @flag;
	Insert into tb3 (f122, f136, f144) 
		values ('Test 3.5.8.5-loop', 11, 8);
		select @counter, @flag;


	delimiter //;

	--error 1064
	Create trigger trg4_2 after update on tb3 for each row
	BEGIN 
		Label1: loop 
			set @counter=@counter+1;
		END;  
	END//
	delimiter ;//
	--error 0, 1360
	drop trigger trg4_2;

#Cleanup
	--disable_warnings 
	drop trigger trg4;
	delete from tb3 where f122='Test 3.5.8.5-loop';
	--enable_warnings

#Section 3.5.8.5 (REPEAT ITERATE)
#Testcase: Ensure that the stored procedure-specific flow control statements 
#          like REPEAT work correctly when they are part of the triggered action
#          portion of a trigger definition.
let $message= Testcase 3.5.8.5-repeat:;
--source include/show_msg.inc
			
	delimiter //;
	Create trigger trg6 after insert on tb3 for each row
	BEGIN
		rp_label: REPEAT 
			SET @counter1 = @counter1 + 1; 
			IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label; 	
			END IF;
			SET @counter2 = @counter2 + 1; 
		UNTIL @counter1> new.f136 END REPEAT rp_label;
	END//
	delimiter ;//

	set @counter1= 0, @counter2= 0;
	Insert into tb3 (f122, f136) 
		values ('Test 3.5.8.5-repeat', 13);
	select @counter1, @counter2;

			
	delimiter //;
	--error 1064 
	Create trigger trg6_2 after update on tb3 for each row
	BEGIN
		REPEAT 
			SET @counter2 = @counter2 + 1; 
	END//
	delimiter ;//

#Cleanup			
	--disable_warnings 
	drop trigger trg6;
	delete from tb3 where f122='Test 3.5.8.5-repeat';
	--enable_warnings


#Section 3.5.8.5 (WHILE)
# Test case: Ensure that the stored procedure-specific flow control 
#            statements WHILE, work correctly when they are part of 
#            the triggered action portion of a trigger definition.
let $message= Testcase 3.5.8.5-while:;
--source include/show_msg.inc

	delimiter //;
	Create trigger trg7 after insert on tb3 for each row
		wl_label: WHILE @counter1 < new.f136 DO 
			SET @counter1 = @counter1 + 1; 
			IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label; 	
			END IF;
			SET @counter2 = @counter2 + 1; 
		END WHILE wl_label//
	delimiter ;//

	set @counter1= 0, @counter2= 0;
	Insert into tb3 (f122, f136) 
		values ('Test 3.5.8.5-while', 7);
	select @counter1, @counter2;
	delimiter //;
	--error 1064 
	Create trigger trg7_2 after update on tb3 for each row
	BEGIN
		WHILE @counter1 < new.f136 
			SET @counter1 = @counter1 + 1; 
	END//
	delimiter ;//

#Cleanup
	--disable_warnings 
	delete from tb3 where f122='Test 3.5.8.5-while';
	drop trigger trg7;
	--enable_warnings

#Section 3.5.8.6
# Test case: Ensure that a trigger definition that includes a CALL to a stored 
#            procedure fails, at CREATE TRIGGER time, with an appropriate error 
#            message
# OBN - requirement void since allowed 
#       Fails due to Bug 9909 the bug allows the trigger to be created
#       and fails in execution time
let $message= Testcase 3.5.8.6: (requirement void);
--source include/show_msg.inc


#Section 3.5.8.7
# Test case: Ensure that a trigger definition that includes a 
#            transaction-delimiting statement (e.g. COMMIT, 
#            ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER 
#            time, with an appropriate error message.
# OBN - Fails due to Bug ____
let $message= Testcase 3.5.8.7: (Disabled as a result of bug _____);
--source include/show_msg.inc

#	--error 1314
#	Create trigger trg9_1 before update on tb3 for each row
#	BEGIN
#		Start transaction;
#			Set new.f120='U';
#		Commit;
#	END;

#	--error 1314
#	Create trigger trg9_2 before delete on tb3 for each row
#	BEGIN
#		Start transaction;
#			Set @var2=old.f120;
#		Rollback;
#	END;

#################################
####### Section 3.5.9 ###########
#   Checks on old and new rows  #
#################################

#Section 3.5.9.1
#Test case: Ensure that every trigger executes its triggered action on each row 
#           that meets the conditions stated in the trigger definition.
#Section 3.5.9.2
#Testcase: Ensure that a trigger never executes its triggered action on any row 
#          that doesn't meet the conditions stated in the trigger definition.
let $message= Testcase 3.5.9.1/2:;
--source include/show_msg.inc

	Create trigger trg1 BEFORE UPDATE on tb3 for each row 
		set new.f142 = 94087, @counter=@counter+1;
--disable_query_log
	select count(*) as TotalRows from tb3;
	select count(*) as Affected from tb3 where f130<100;
	select count(*) as NotAffected from tb3 where f130>=100;
	select count(*) as NewValuew from tb3 where f142=94087;
--enable_query_log
	set @counter=0;
	Update tb3 Set f142='1' where f130<100;
	select count(*) as ExpectedChanged, @counter as TrigCounter 
		from tb3 where f142=94087;
	select count(*) as ExpectedNotChange from tb3 
		where f130<100 and f142<>94087;
	select count(*) as NonExpectedChanged from tb3 
		where f130>=130 and f142=94087;

#Cleanup
	--disable_warnings 
	drop trigger trg1;
	--enable_warnings


#Section 3.5.9.3
#Test case: Ensure that a reference to OLD.<column name> always correctly refers 
#           to the values of the specified column of the subject table before a 
#           data row is updated or deleted.
let $message= Testcase 3.5.9.3:;
--source include/show_msg.inc
	
	Create trigger trg2_a before update on tb3 for each row
		set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
		    @tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
		    @tr_var_b4_163=old.f163;

	Create trigger trg2_b after update on tb3 for each row
		set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
		    @tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
		    @tr_var_af_163=old.f163;

	Create trigger trg2_c before delete on tb3 for each row
		set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
		    @tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
		    @tr_var_b4_163=old.f163;

	Create trigger trg2_d after delete on tb3 for each row
		set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
		    @tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
		    @tr_var_af_163=old.f163;


--disable_query_log
	set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, 
		@tr_var_b4_136=0, @tr_var_b4_163=0;
	set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, 
		@tr_var_af_136=0, @tr_var_af_163=0;
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_163;
--enable_query_log

	Insert into tb3 (f122, f136, f163) 
		values ('Test 3.5.9.3', 7, 123.17);
	Update tb3 Set f136=8 where f122='Test 3.5.9.3';

	select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3';
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_163;

--disable_query_log
	set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, 
		@tr_var_b4_136=0, @tr_var_b4_163=0;
	set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, 
		@tr_var_af_136=0, @tr_var_af_163=0;
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_163;
--enable_query_log

	delete from tb3 where f122='Test 3.5.9.3';

	select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3';
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_163;
#Cleanup
	--disable_warnings 
	drop trigger trg2_a;
	drop trigger trg2_b;
	drop trigger trg2_c;
	drop trigger trg2_d;
	--enable_warnings

#Section 3.5.9.4
#Test case: Ensure that a reference to NEW.<column name> always correctly refers 
#           to the values of the specified column of the subject table after an 
#           existing data row has been updated or a new data row has been inserted.
let $message= Testcase 3.5.9.4:;
--source include/show_msg.inc
	
	Create trigger trg3_a before insert on tb3 for each row
		set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
		    @tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
		    @tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;

	Create trigger trg3_b after insert on tb3 for each row
		set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
		    @tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
		    @tr_var_af_151=new.f151, @tr_var_af_163=new.f163;

	Create trigger trg3_c before update on tb3 for each row
		set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
		    @tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
		    @tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;

	Create trigger trg3_d after update on tb3 for each row
		set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
		    @tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
		    @tr_var_af_151=new.f151, @tr_var_af_163=new.f163;

--disable_query_log
	set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, 
		@tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
	set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, 
		@tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
--enable_query_log

	Insert into tb3 (f122, f136, f151, f163) 
		values ('Test 3.5.9.4', 7, DEFAULT, 995.24);

	select f118, f121, f122, f136, f151, f163 from tb3 
		where f122 like 'Test 3.5.9.4%';
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;

--disable_query_log
	set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, 
		@tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
	set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, 
		@tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
--enable_query_log

	Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
		 where f122='Test 3.5.9.4';

	select f118, f121, f122, f136, f151, f163 from tb3 
		where f122 like 'Test 3.5.9.4-trig';
	select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
		@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
	select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
		@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
#Cleanup
	--disable_warnings 
	drop trigger trg3_a;
	drop trigger trg3_b;
	drop trigger trg3_c;
	drop trigger trg3_d;
	delete from tb3 where f122='Test 3.5.9.4-trig';
	--enable_warnings


#Section 3.5.9.5
# Test case: Ensure that the definition of an INSERT trigger can include a 
#            reference to NEW. <Column name>.
let $message= Testcase 3.5.9.5: (implied in previous tests);
--source include/show_msg.inc
	
#Section 3.5.9.6
# Test case: Ensure that the definition of an INSERT trigger cannot include 
#            a reference to OLD. <Column name>.
let $message= Testcase 3.5.9.6:;
--source include/show_msg.inc
	
	--error 1363
	create trigger trg4a before insert on tb3 for each row
		set @temp1= old.f120;
	--error 1362
	create trigger trg4b after insert on tb3 for each row
		set old.f120= 'test';

#Cleanup
	--disable_warnings 
	--error 0, 1360
	drop trigger trg4a;
	--error 0, 1360
	drop trigger trg4b;
	--enable_warnings


#Section 3.5.9.7
# Test case: Ensure that the definition of an UPDATE trigger can include a 
#            reference to NEW. <Column name>.
let $message= Testcase 3.5.9.7: (implied in previous tests);
--source include/show_msg.inc
	
#Section 3.5.9.8
# Test case: Ensure that the definition of an UPDATE trigger cannot include a 
#            reference to OLD. <Column name>.
let $message= Testcase 3.5.9.8: (implied in previous tests);
--source include/show_msg.inc
	
#Section 3.5.9.9
# Test case: Ensure that the definition of a DELETE trigger cannot include a 
#            reference to NEW.<column name>.
let $message= Testcase 3.5.9.9:;
--source include/show_msg.inc
	
	--error 1363
	create trigger trg5a before DELETE on tb3 for each row
		set @temp1=new.f122;
	--error 1363
	create trigger trg5b after DELETE on tb3 for each row
		set new.f122='test';
let $message= The above returns the wrong error, should be error 1362 (Bug 11648)
--source include/show_msg.inc

#Cleanup
	--disable_warnings 
	--error 0, 1360
	drop trigger trg5a;
	--error 0, 1360
	drop trigger trg5b;
	--enable_warnings


#Section 3.5.9.10
# Test case: Ensure that the definition of a DELETE trigger can include a reference 
#            to OLD.<column name>.
let $message= Testcase 3.5.9.10: (implied in previous tests);
--source include/show_msg.inc
	

#Section 3.5.9.11
# Testcase: Ensure that trigger definition that includes a referance to 
#           NEW.<colunm name> fails with an appropriate error message, 
#           at CREATE TRIGGER time, if the trigger event in not INSERT or UPDATE
let $message= Testcase 3.5.9.11: covered by 3.5.9.9;
--source include/show_msg.inc


#Section 3.5.9.12
# Testcase: Ensure that trigger definition that includes a referance to 
#           OLD.<column name> fails with an appropriate error message, at 
#           CREATE TRIGGER time, if the trigger event is not DELETE or UPDATE
let $message= Testcase 3.5.9.12: covered by 3.5.9.6;
--source include/show_msg.inc


#Section 3.5.9.13
# Test case: Ensure that all references to OLD. <Column name> are read-only, 
#            that is, that they cannot be used to modify a data row.
let $message= Testcase 3.5.9.13:;
--source include/show_msg.inc
	
	--error 1362
	create trigger trg6a before UPDATE on tb3 for each row
		set old.f118='C', new.f118='U';
	--error 1362
	create trigger trg6b after INSERT on tb3 for each row
		set old.f136=163, new.f118='U';
	--error 1362
	create trigger trg6c after UPDATE on tb3 for each row
		set old.f136=NULL;

#Cleanup
	--disable_warnings 
	--error 0, 1360
	drop trigger trg6a;
	--error 0, 1360
	drop trigger trg6b;
	--error 0, 1360
	drop trigger trg6c;
	--enable_warnings


#Section 3.5.9.14
# Test case: Ensure that all references to NEW. <Column name> may be used both to 
#            read a data row and to modify a data row
let $message= Testcase 3.5.9.14: (implied in previous tests);
--source include/show_msg.inc

	
##############################################
################ 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%';
	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%';
	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%';
	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
	
	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 $MYSQL_TEST_DIR MYSQL_TEST_DIR
        eval load data infile '$MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load;

	select @counter as 'Rows Loaded After';
	Select * from tb_load 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

	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 1329
	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

##################################
########## 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

# Cleanup section 3.5
	connection default;
	drop user test_general@localhost;
	drop user test_general;
	drop user test_super@localhost;

##########################################
# 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

	create table t1 (f1 integer);
	create table t2_1 (f1 integer);
	create table t2_2 (f1 integer);
	create table t2_3 (f1 integer);
	create table t2_4 (f1 integer);
	create table t3 (f1 integer);

	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;

#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 
	eval create table t1 (f1 integer) engine = $engine_type;
	eval create table t2 (f2 integer) engine = $engine_type;
	eval create table t3 (f3 integer) engine = $engine_type;
	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);

	# OBN See bug 11896
	--error 1442
	insert into t1 values (1);
	select * from t1;
	select * from t2;
	select * from t3;
	select * from t4;

#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 (disabled bug 11889);
--source include/show_msg.inc

set @sql_mode='traditional';
	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 1424
	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


# Testcase: y.y.y.5:
# Checking rollback of nested trigger definitions
let $message= Testcase y.y.y.5: Roleback 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 
	eval create table t1 (f1 integer) engine = $engine_type;
	eval create table t2 (f2 integer) engine = $engine_type;
	eval create table t3 (f3 integer) engine = $engine_type;
	eval create table t4 (f4 tinyint) engine = $engine_type;
	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);

#lock tables t1 write, t2 write, t3 write, t4 write;
	
	set autocommit=0;
	start transaction;
	--error 1264
	insert into t1 values (1);
	commit;
	select * from t1;
	select * from t2;
	select * from t3;
#unlock tables;
#Cleanup 
	--disable_warnings 
	drop trigger tr1;
	drop trigger tr2;
	drop trigger tr3;
	drop table t1;
	drop table t2;
	drop table t3;
	drop table t4;
	--enable_warnings 
