~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) 
#======================================================================
# WL#4084: enable disabled parts, 2007-11-15 hhunger

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

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

#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 con2_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 con2_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';
	--sorted_result
	select * from db_test.t1_i;
	--sorted_result
	select * from db_test.t1_u;
	--sorted_result
	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 con2_super;
        delimiter //;
	Create trigger trg2 BEFORE UPDATE on tb3 for each row
        BEGIN
		insert into db_test.t1_i 
			values (new.f120, new.f136, new.f144, new.f163);
        END//
        delimiter ;//

# Trigger exeution - single SQL Insert
	connection con2_general;
        Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
        select * from db_test.t1_i order by i120;
	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 order by i120;


let $message= 3.5.8.4 - single SQL - update;
--source include/show_msg.inc
# Trigger definition - single SQL update
	connection con2_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 con2_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 order by u120;


let $message= 3.5.8.3/4 - single SQL - delete;
--source include/show_msg.inc
# Trigger definition - single SQL delete
	connection con2_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 con2_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 order by d120;


let $message= 3.5.8.3/4 - single SQL - select;
--source include/show_msg.inc
# Trigger definition - single SQL select
	connection con2_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 con2_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' order by f136;
	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' order by f136;
	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' order by f136;
	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' order by f136;

	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' order by f120,f136;
	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' order by f120,f136;
	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' order by f120,f136;
	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' order by f120,f136;
	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' order by f120,f136;
	--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' order by f120,f136;

	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. Not more valid requirement.
let $message= Testcase 3.5.8.6: (requirement void);
--source include/show_msg.inc
	delimiter //;
	CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//

	CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
	BEGIN
		CALL sp_01 ();
	END//
	delimiter ;//
	Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
	update tb3 set f120='S', f136=111,
	       f122='Test 3.5.8.6-tr8_1'
	       where f122='Test 3.5.8.6-insert';
	select f120, f122
	       from tb3 where f122  like 'Test 3.5.8.6%' order by f120;
	DROP TRIGGER trg8_1;
	DROP PROCEDURE sp_01;


#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.
let $message= Testcase 3.5.8.7;
--source include/show_msg.inc
	
	delimiter //;
	--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG 
	Create trigger trg9_1 before update on tb3 for each row
	BEGIN
		Start transaction;
			Set new.f120='U';
		Commit;
	END//

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


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



1 by brian clean slate	1	#======================================================================
	2	#
	3	# Trigger Tests
	4	# (test case numbering refer to requirement document TP v1.1)
	5	#======================================================================
	6	# WL#4084: enable disabled parts, 2007-11-15 hhunger
	7
	8	# General setup for Trigger tests
	9	let $message= Testcase: 3.5:;
	10	--source include/show_msg.inc
	11
	12	--disable_abort_on_error
	13
	14	create User test_general@localhost;
	15	set password for test_general@localhost = password('PWD');
	16	revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
	17
	18	create User test_super@localhost;
	19	set password for test_super@localhost = password('PWD');
	20	grant ALL on . to test_super@localhost with grant OPTION;
	21	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	22	connect (con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	23	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	24	connect (con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	25	connection default;
	26
	27	#################################
	28	####### Section 3.5.8 ###########
	29	# Checks on Triggered Actions #
	30	#################################
	31
	32	#Section 3.5.8.1
	33	# Testcase: Ensure that the triggered action of every trigger always executes
	34	# correctly and the results in all expected changes made to the database
	35	let $message= Testcase 3.5.8.1: (implied in previous tests);
	36	--source include/show_msg.inc
	37
	38	#Section 3.5.8.2
	39	# Testcase: Ensure that the triggered actions of every trigger never results
	40	# in an unexpected change made to the database.
	41	let $message= Testcase 3.5.8.2: (implied in previous tests);
	42	--source include/show_msg.inc
	43
	44
	45	#Section 3.5.8.3 / 3.5.8.4
	46	#Test case: Ensure that the triggered action can any valid SQL statement / set
	47	# of valid SQL statements, provided the statements are written within
	48	# a BEGIN/END compound statement construct
	49	# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
	50	# as there are the most likely to be used in triggers
	51	let $message= Testcase 3.5.8.3/4:;
	52	--source include/show_msg.inc
	53
	54	# creating test tables to perform the trigger SQL on
	55	connection con2_super;
	56	create database db_test;
	57	grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
	58	grant LOCK TABLES on db_test.* to test_general;
	59	Use db_test;
	60	eval create table t1_i (
	61	i120 char ascii not null DEFAULT b'101',
	62	i136 smallint zerofill not null DEFAULT 999,
	63	i144 int zerofill not null DEFAULT 99999,
	64	i163 decimal (63,30)) engine=$engine_type;
65	eval create table t1_u (
66	u120 char ascii not null DEFAULT b'101',
67	u136 smallint zerofill not null DEFAULT 999,
68	u144 int zerofill not null DEFAULT 99999,
69	u163 decimal (63,30)) engine=$engine_type;
70	eval create table t1_d (
71	d120 char ascii not null DEFAULT b'101',
72	d136 smallint zerofill not null DEFAULT 999,
73	d144 int zerofill not null DEFAULT 99999,
74	d163 decimal (63,30)) engine=$engine_type;
75	Insert into t1_u values ('a',111,99999,999.99);
76	Insert into t1_u values ('b',222,99999,999.99);
77	Insert into t1_u values ('c',333,99999,999.99);
78	Insert into t1_u values ('d',222,99999,999.99);
79	Insert into t1_u values ('e',222,99999,999.99);
80	Insert into t1_u values ('f',333,99999,999.99);
81	Insert into t1_d values ('a',111,99999,999.99);
82	Insert into t1_d values ('b',222,99999,999.99);
83	Insert into t1_d values ('c',333,99999,999.99);
84	Insert into t1_d values ('d',444,99999,999.99);
85	Insert into t1_d values ('e',222,99999,999.99);
86	Insert into t1_d values ('f',222,99999,999.99);
87
88	let $message= 3.5.8.4 - multiple SQL;
89	--source include/show_msg.inc
90	# Trigger definition - multiple SQL
91	use test;
92	delimiter //;
93	Create trigger trg1 AFTER INSERT on tb3 for each row
94	BEGIN
95	insert into db_test.t1_i
96	values (new.f120, new.f136, new.f144, new.f163);
97	update db_test.t1_u
98	set u144=new.f144, u163=new.f163
99	where u136=new.f136;
100	delete from db_test.t1_d where d136= new.f136;
101	select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
102	where u136= new.f136;
103	END//
104	delimiter ;//
105
106	# Test trigger execution - multiple SQL
107	connection con2_general;
108	Use test;
109	set @test_var=0;
110	Insert into tb3 (f120, f122, f136, f144, f163)
111	values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
112	Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
113	--sorted_result
114	select * from db_test.t1_i;
115	--sorted_result
116	select * from db_test.t1_u;
117	--sorted_result
118	select * from db_test.t1_d;
119	select @test_var;
120
121
122	let $message= 3.5.8.4 - single SQL - insert;
123	--source include/show_msg.inc
124	# Trigger definition - single SQL Insert
125	connection con2_super;
126	delimiter //;
127	Create trigger trg2 BEFORE UPDATE on tb3 for each row
128	BEGIN
129	insert into db_test.t1_i
130	values (new.f120, new.f136, new.f144, new.f163);
131	END//
132	delimiter ;//
133
134	# Trigger exeution - single SQL Insert
135	connection con2_general;
136	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
137	select * from db_test.t1_i order by i120;
138	update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
139	where f122='Test 3.5.8.4';
140	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
141	select * from db_test.t1_i order by i120;
142
143
144	let $message= 3.5.8.4 - single SQL - update;
145	--source include/show_msg.inc
146	# Trigger definition - single SQL update
147	connection con2_super;
148	drop trigger trg2;
149	Create trigger trg3 BEFORE UPDATE on tb3 for each row
150	update db_test.t1_u
151	set u120=new.f120
152	where u136=new.f136;
153
154	# Trigger exeution - single SQL - update;
155	connection con2_general;
156	update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
157	where f122='Test 3.5.8.4-Single Insert';
158	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
159	select * from db_test.t1_u order by u120;
160
161
162	let $message= 3.5.8.3/4 - single SQL - delete;
163	--source include/show_msg.inc
164	# Trigger definition - single SQL delete
165	connection con2_super;
166	drop trigger trg3;
167	Create trigger trg4 AFTER UPDATE on tb3 for each row
168	delete from db_test.t1_d where d136= new.f136;
169
170	# Trigger exeution - single SQL delete
171	connection con2_general;
172	#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
173	update tb3 set f120='D', f136=444,
174	f122='Test 3.5.8.4-Single Delete'
175	where f122='Test 3.5.8.4-Single Update';
176	#unlock tables;
177	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
178	select * from db_test.t1_d order by d120;
179
180
181	let $message= 3.5.8.3/4 - single SQL - select;
182	--source include/show_msg.inc
183	# Trigger definition - single SQL select
184	connection con2_super;
185	drop trigger trg4;
186	Create trigger trg5 AFTER UPDATE on tb3 for each row
187	select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
188	where u136= new.f136;
189
190	# Trigger exeution - single SQL select
191	connection con2_general;
192	set @test_var=0;
193	update tb3 set f120='S', f136=111,
194	f122='Test 3.5.8.4-Single Select'
195	where f122='Test 3.5.8.4-Single Delete';
196	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
197	select @test_var;
198
199	#Cleanup
200	connection default;
201	--disable_warnings
202	drop trigger trg1;
203	drop trigger trg5;
204	drop database if exists db_test;
205	delete from tb3 where f122 like 'Test 3.5.8.4%';
206	revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
207	--enable_warnings
208
209
210	#Section 3.5.8.5 (IF)
211	# Test case: Ensure that the stored procedure-specific flow control statement like IF
212	# works correctly when it is a part of the triggered action portion of a
213	# trigger definition.
214	let $message= Testcase 3.5.8.5 (IF):;
215	--source include/show_msg.inc
216
217	delimiter //;
218	create trigger trg2 before insert on tb3 for each row
219	BEGIN
220	IF new.f120='1' then
221	set @test_var='one', new.f120='2';
222	ELSEIF new.f120='2' then
223	set @test_var='two', new.f120='3';
224	ELSEIF new.f120='3' then
225	set @test_var='three', new.f120='4';
226	END IF;
227
228	IF (new.f120='4') and (new.f136=10) then
229	set @test_var2='2nd if', new.f120='d';
230	ELSE
231	set @test_var2='2nd else', new.f120='D';
232	END IF;
233	END//
234	delimiter ;//
235
236	set @test_var='Empty', @test_var2=0;
237	Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
238	select f120, f122, f136, @test_var, @test_var2
239	from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
240	Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
241	select f120, f122, f136, @test_var, @test_var2
242	from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
243	Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
244	select f120, f122, f136, @test_var, @test_var2
245	from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
246	Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
247	select f120, f122, f136, @test_var, @test_var2
248	from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
249
250	delimiter //;
251	--error 1064
252	create trigger trg3 before update on tb3 for each row
253	BEGIN
254	ELSEIF new.f120='2' then
255	END IF;
256	END//
257	--error 0, 1360
258	drop trigger trg3//
259
260	--error 1064
261	create trigger trg4 before update on tb3 for each row
262	BEGIN
263	IF (new.f120='4') and (new.f136=10) then
264	set @test_var2='2nd if', new.f120='d';
265	ELSE
266	set @test_var2='2nd else', new.f120='D';
267	END//
268	delimiter ;//
269	--error 0, 1360
270	drop trigger trg4;
271
272	#Cleanup
273	--disable_warnings
274	drop trigger trg2;
275	delete from tb3 where f121='Test 3.5.8.5-if';
276	--enable_warnings
277
278
279	#Section 3.5.8.5 (CASE)
280	# Test case: Ensure that the stored procedure-specific flow control statement
281	# like CASE works correctly when it is a part of the triggered action
282	# portion of a trigger definition.
283	let $message= Testcase 3.5.8.5-case:;
284	--source include/show_msg.inc
285
286	delimiter //;
287	create trigger trg3 before insert on tb3 for each row
288	BEGIN
289	SET new.f120=char(ascii(new.f120)-32);
290	CASE
291	when new.f136<100 then set new.f136=new.f136+120;
292	when new.f136<10 then set new.f144=777;
293	when new.f136>100 then set new.f120=new.f136-1;
294	END case;
295	CASE
296	when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
297	ELSE set @test_var=concat(new.f120, '*');
298	END case;
299	CASE new.f144
300	when 1 then set @test_var=concat(@test_var, 'one');
301	when 2 then set @test_var=concat(@test_var, 'two');
302	when 3 then set @test_var=concat(@test_var, 'three');
303	when 4 then set @test_var=concat(@test_var, 'four');
304	when 5 then set @test_var=concat(@test_var, 'five');
305	when 6 then set @test_var=concat(@test_var, 'six');
306	when 7 then set @test_var=concat(@test_var, 'seven');
307	when 8 then set @test_var=concat(@test_var, 'eight');
308	when 9 then set @test_var=concat(@test_var, 'nine');
309	when 10 then set @test_var=concat(@test_var, 'ten');
310	when 11 then set @test_var=concat(@test_var, 'eleven');
311	when 12 then set @test_var=concat(@test_var, 'twelve');
312	when 13 then set @test_var=concat(@test_var, 'thirteen');
313	when 14 then set @test_var=concat(@test_var, 'fourteen');
314	when 15 then set @test_var=concat(@test_var, 'fifteen');
315	ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
316	END case;
317	END//
318	delimiter ;//
319
320	set @test_var='Empty';
321	Insert into tb3 (f120, f122, f136, f144)
322	values ('a', 'Test 3.5.8.5-case', 5, 7);
323	select f120, f122, f136, f144, @test_var
324	from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
325	Insert into tb3 (f120, f122, f136, f144)
326	values ('b', 'Test 3.5.8.5-case', 71,16);
327	select f120, f122, f136, f144, @test_var
328	from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
329	Insert into tb3 (f120, f122, f136, f144)
330	values ('c', 'Test 3.5.8.5-case', 80,1);
331	select f120, f122, f136, f144, @test_var
332	from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
333	Insert into tb3 (f120, f122, f136)
334	values ('d', 'Test 3.5.8.5-case', 152);
335	select f120, f122, f136, f144, @test_var
336	from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
337	Insert into tb3 (f120, f122, f136, f144)
338	values ('e', 'Test 3.5.8.5-case', 200, 8);
339	select f120, f122, f136, f144, @test_var
340	from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
341	--error 0, 1339
342	Insert into tb3 (f120, f122, f136, f144)
343	values ('f', 'Test 3.5.8.5-case', 100, 8);
344	select f120, f122, f136, f144, @test_var
345	from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
346
347	delimiter //;
348	--error 1064
349	create trigger trg3a before update on tb3 for each row
350	BEGIN
351	CASE
352	when new.f136<100 then set new.f120='p';
353	END//
354	delimiter ;//
355
356	--error 0, 1360
357	drop trigger trg3a;
358
359	#Cleanup
360	--disable_warnings
361	drop trigger trg3;
362	delete from tb3 where f121='Test 3.5.8.5-case';
363	--enable_warnings
364
365	#Section 3.5.8.5 (LOOP)
366	# Test case: Ensure that the stored procedure-specific flow control
367	# statement like LOOP / LEAVE work correctly when they are
368	# part of the triggered action portion of a trigger definition.
369	let $message= Testcase 3.5.8.5-loop/leave:;
370	--source include/show_msg.inc
371
372	delimiter //;
373	Create trigger trg4 after insert on tb3 for each row
374	BEGIN
375	set @counter=0, @flag='Initial';
376	Label1: loop
377	if new.f136<new.f144 then
378	set @counter='Nothing to loop';
379	leave Label1;
380	else
381	set @counter=@counter+1;
382	if new.f136=new.f144+@counter then
383	set @counter=concat(@counter, ' loops');
384	leave Label1;
385	end if;
386	end if;
387	iterate label1;
388	set @flag='Final';
389	END loop Label1;
390	END//
391	delimiter ;//
392	Insert into tb3 (f122, f136, f144)
393	values ('Test 3.5.8.5-loop', 2, 8);
394	select @counter, @flag;
395	Insert into tb3 (f122, f136, f144)
396	values ('Test 3.5.8.5-loop', 11, 8);
397	select @counter, @flag;
398
399
400	delimiter //;
401
402	--error 1064
403	Create trigger trg4_2 after update on tb3 for each row
404	BEGIN
405	Label1: loop
406	set @counter=@counter+1;
407	END;
408	END//
409	delimiter ;//
410	--error 0, 1360
411	drop trigger trg4_2;
412
413	#Cleanup
414	--disable_warnings
415	drop trigger trg4;
416	delete from tb3 where f122='Test 3.5.8.5-loop';
417	--enable_warnings
418
419	#Section 3.5.8.5 (REPEAT ITERATE)
420	#Testcase: Ensure that the stored procedure-specific flow control statements
421	# like REPEAT work correctly when they are part of the triggered action
422	# portion of a trigger definition.
423	let $message= Testcase 3.5.8.5-repeat:;
424	--source include/show_msg.inc
425
426	delimiter //;
427	Create trigger trg6 after insert on tb3 for each row
428	BEGIN
429	rp_label: REPEAT
430	SET @counter1 = @counter1 + 1;
431	IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
432	END IF;
433	SET @counter2 = @counter2 + 1;
434	UNTIL @counter1> new.f136 END REPEAT rp_label;
435	END//
436	delimiter ;//
437
438	set @counter1= 0, @counter2= 0;
439	Insert into tb3 (f122, f136)
440	values ('Test 3.5.8.5-repeat', 13);
441	select @counter1, @counter2;
442
443
444	delimiter //;
445	--error 1064
446	Create trigger trg6_2 after update on tb3 for each row
447	BEGIN
448	REPEAT
449	SET @counter2 = @counter2 + 1;
450	END//
451	delimiter ;//
452
453	#Cleanup
454	--disable_warnings
455	drop trigger trg6;
456	delete from tb3 where f122='Test 3.5.8.5-repeat';
457	--enable_warnings
458
459
460	#Section 3.5.8.5 (WHILE)
461	# Test case: Ensure that the stored procedure-specific flow control
462	# statements WHILE, work correctly when they are part of
463	# the triggered action portion of a trigger definition.
464	let $message= Testcase 3.5.8.5-while:;
465	--source include/show_msg.inc
466
467	delimiter //;
468	Create trigger trg7 after insert on tb3 for each row
469	wl_label: WHILE @counter1 < new.f136 DO
470	SET @counter1 = @counter1 + 1;
471	IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
472	END IF;
473	SET @counter2 = @counter2 + 1;
474	END WHILE wl_label//
475	delimiter ;//
476
477	set @counter1= 0, @counter2= 0;
478	Insert into tb3 (f122, f136)
479	values ('Test 3.5.8.5-while', 7);
480	select @counter1, @counter2;
481	delimiter //;
482	--error 1064
483	Create trigger trg7_2 after update on tb3 for each row
484	BEGIN
485	WHILE @counter1 < new.f136
486	SET @counter1 = @counter1 + 1;
487	END//
488	delimiter ;//
489
490	#Cleanup
491	--disable_warnings
492	delete from tb3 where f122='Test 3.5.8.5-while';
493	drop trigger trg7;
494	--enable_warnings
495
496	#Section 3.5.8.6
497	# Test case: Ensure that a trigger definition that includes a CALL to a stored
498	# procedure fails, at CREATE TRIGGER time, with an appropriate error
499	# message. Not more valid requirement.
500	let $message= Testcase 3.5.8.6: (requirement void);
501	--source include/show_msg.inc
502	delimiter //;
503	CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
504
505	CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
506	BEGIN
507	CALL sp_01 ();
508	END//
509	delimiter ;//
510	Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
511	update tb3 set f120='S', f136=111,
512	f122='Test 3.5.8.6-tr8_1'
513	where f122='Test 3.5.8.6-insert';
514	select f120, f122
515	from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
516	DROP TRIGGER trg8_1;
517	DROP PROCEDURE sp_01;
518
519
520	#Section 3.5.8.7
521	# Test case: Ensure that a trigger definition that includes a
522	# transaction-delimiting statement (e.g. COMMIT,
523	# ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER
524	# time, with an appropriate error message.
525	let $message= Testcase 3.5.8.7;
526	--source include/show_msg.inc
527
528	delimiter //;
529	--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
530	Create trigger trg9_1 before update on tb3 for each row
531	BEGIN
532	Start transaction;
533	Set new.f120='U';
534	Commit;
535	END//
536
537	--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
538	Create trigger trg9_2 before delete on tb3 for each row
539	BEGIN
540	Start transaction;
541	Set @var2=old.f120;
542	Rollback;
543	END//
544	delimiter ;//
545
546
547	# Cleanup section 3.5
548	connection default;
549	drop user test_general@localhost;
550	drop user test_general;
551	drop user test_super@localhost;
552
553