triggers_08.inc

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· INC 代码 · 共 532 行 · 第 1/2 页

	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;


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