memory_triggers.result

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

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%';
f120	f122	f136	f144	f163
U	Test 3.5.8.4-Single Update	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_u;
u120	u136	u144	u163
a	00111	0000099999	999.990000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
f	00333	0000099999	999.990000000000000000000000000000

3.5.8.3/4 - single SQL - delete
-------------------------------
drop trigger trg3;
Create trigger trg4 AFTER UPDATE on tb3 for each row
delete from db_test.t1_d where d136= new.f136;
update tb3 set f120='D', f136=444, 
f122='Test 3.5.8.4-Single Delete'
		 where f122='Test 3.5.8.4-Single Update';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
D	Test 3.5.8.4-Single Delete	00444	0000023456	1.050000000000000000000000000000
select * from db_test.t1_d;
d120	d136	d144	d163
a	00111	0000099999	999.990000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000

3.5.8.3/4 - single SQL - select
-------------------------------
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;
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%';
f120	f122	f136	f144	f163
S	Test 3.5.8.4-Single Select	00111	0000023456	1.050000000000000000000000000000
select @test_var;
@test_var
999.990000000000000000000000000000
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';

Testcase 3.5.8.5 (IF):
----------------------
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//
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';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	one	2nd else
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';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	two	2nd else
D	Test 3.5.8.5-if	00102	two	2nd else
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';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	three	2nd if
D	Test 3.5.8.5-if	00102	three	2nd if
d	Test 3.5.8.5-if	00010	three	2nd 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';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	three	2nd else
D	Test 3.5.8.5-if	00102	three	2nd else
d	Test 3.5.8.5-if	00010	three	2nd else
D	Test 3.5.8.5-if	00103	three	2nd else
create trigger trg3 before update on tb3 for each row
BEGIN
ELSEIF new.f120='2' then
END IF;
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF new.f120='2' then
END IF;
END' at line 3
drop trigger trg3//
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//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7
drop trigger trg4;
drop trigger trg2;
delete from tb3 where f121='Test 3.5.8.5-if';

Testcase 3.5.8.5-case:
----------------------
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//
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';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	A*seven
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';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	B*0000000016
B	Test 3.5.8.5-case	00191	0000000016	B*0000000016
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';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	C=one
B	Test 3.5.8.5-case	00191	0000000016	C=one
C	Test 3.5.8.5-case	00200	0000000001	C=one
Insert into tb3 (f120, f122, f136) 
values ('d', 'Test 3.5.8.5-case', 152);
Warnings:
Warning	1265	Data truncated for column 'f120' at row 1
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	1*0000099999
B	Test 3.5.8.5-case	00191	0000000016	1*0000099999
C	Test 3.5.8.5-case	00200	0000000001	1*0000099999
1	Test 3.5.8.5-case	00152	0000099999	1*0000099999
Insert into tb3 (f120, f122, f136, f144) 
values ('e', 'Test 3.5.8.5-case', 200, 8);
Warnings:
Warning	1265	Data truncated for column 'f120' at row 1
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	1=eight
B	Test 3.5.8.5-case	00191	0000000016	1=eight
C	Test 3.5.8.5-case	00200	0000000001	1=eight
1	Test 3.5.8.5-case	00152	0000099999	1=eight
1	Test 3.5.8.5-case	00200	0000000008	1=eight
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';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	1=eight
B	Test 3.5.8.5-case	00191	0000000016	1=eight
C	Test 3.5.8.5-case	00200	0000000001	1=eight
1	Test 3.5.8.5-case	00152	0000099999	1=eight
1	Test 3.5.8.5-case	00200	0000000008	1=eight
create trigger trg3a before update on tb3 for each row
BEGIN
CASE
when new.f136<100 then set new.f120='p';
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
drop trigger trg3a;
drop trigger trg3;
delete from tb3 where f121='Test 3.5.8.5-case';

Testcase 3.5.8.5-loop/leave:
----------------------------
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//
Insert into tb3 (f122, f136, f144) 
values ('Test 3.5.8.5-loop', 2, 8);
select @counter, @flag;
@counter	@flag
Nothing to loop	Initial
Insert into tb3 (f122, f136, f144) 
values ('Test 3.5.8.5-loop', 11, 8);
select @counter, @flag;
@counter	@flag
3 loops	Initial
Create trigger trg4_2 after update on tb3 for each row
BEGIN 
Label1: loop 
set @counter=@counter+1;
END;  
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';  
END' at line 5
drop trigger trg4_2;
drop trigger trg4;
delete from tb3 where f122='Test 3.5.8.5-loop';

Testcase 3.5.8.5-repeat:
------------------------
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//
set @counter1= 0, @counter2= 0;
Insert into tb3 (f122, f136) 
values ('Test 3.5.8.5-repeat', 13);
select @counter1, @counter2;
@counter1	@counter2
15	8
Create trigger trg6_2 after update on tb3 for each row
BEGIN
REPEAT 
SET @counter2 = @counter2 + 1; 
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 5
drop trigger trg6;
delete from tb3 where f122='Test 3.5.8.5-repeat';

Testcase 3.5.8.5-while:
-----------------------
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//
set @counter1= 0, @counter2= 0;
Insert into tb3 (f122, f136) 
values ('Test 3.5.8.5-while', 7);
select @counter1, @counter2;
@counter1	@counter2
7	4
Create trigger trg7_2 after update on tb3 for each row
BEGIN
WHILE @counter1 < new.f136 
SET @counter1 = @counter1 + 1; 
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @counter1 = @counter1 + 1; 
END' at line 4
delete from tb3 where f122='Test 3.5.8.5-while';
drop trigger trg7;

Testcase 3.5.8.6: (requirement void)
------------------------------------

Testcase 3.5.8.7: (Disabled as a result of bug _____)
-----------------------------------------------------

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

Testcase 3.5.9.3:
-----------------
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;
Crea