Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_trigger_sp.inc

raw
1 ################################################################################
2 # inc/gcol_trigger_sp.inc #
3 # #
4 # Purpose: #
5 # Testing triggers, stored procedures and functions #
6 # defined on tables with generated columns. #
7 # #
8 # #
9 # #
10 #------------------------------------------------------------------------------#
11 # Original Author: Andrey Zhakov #
12 # Original Date: 2008-09-04 #
13 # Change Author: #
14 # Change Date: #
15 # Change: #
16 ################################################################################
17
18 create table t1 (a int,
19 b int generated always as (a/10) virtual,
20 c int generated always as (a/10) stored);
21
22 create table t2 (a timestamp);
23
24 delimiter |;
25
26 create trigger trg1 before insert on t1 for each row
27 begin
28 if (new.b < 10) then
29 set new.a:= 100;
30 set new.b:= 9;
31 set new.c:= 9;
32 end if;
33
34 if (new.c > 50) then
35 set new.a:= 500;
36 end if;
37 end|
38
39 create trigger trg2 after insert on t1 for each row
40 begin
41 if (new.b >= 60) then
42 insert into t2 values (now());
43 end if;
44 end|
45
46 create function f1()
47 returns int
48 begin
49 declare sum1 int default '0';
50 declare cur1 cursor for select sum(b) from t1;
51 open cur1;
52 fetch cur1 into sum1;
53 close cur1;
54 return sum1;
55 end|
56
57 delimiter ;|
58
59 set sql_warnings = 1;
60
61 insert into t1 (a) values (200);
62 select * from t1;
63 select * from t2;
64
65 insert into t1 (a) values (10);
66 --sorted_result
67 select * from t1;
68 select * from t2;
69
70 insert into t1 (a) values (600);
71 --sorted_result
72 select * from t1;
73 --replace_column 1 <timestamp>
74 select * from t2;
75
76 select f1();
77
78 set sql_warnings = 0;
79
80 drop trigger trg1;
81 drop trigger trg2;
82 drop table t2;
83
84 delimiter |;
85
86 create procedure p1()
87 begin
88 declare i int default '0';
89 create table t2 like t1;
90 insert into t2 (a) values (100), (200);
91 begin
92 declare cur1 cursor for select sum(c) from t2;
93 open cur1;
94 fetch cur1 into i;
95 close cur1;
96 if (i=30) then
97 insert into t1 values (300,default,default);
98 end if;
99 end;
100 end|
101
102 delimiter ;|
103
104 delete from t1;
105
106 call p1();
107
108 --sorted_result
109 select * from t2;
110 --sorted_result
111 select * from t1;
112
113 drop table t1,t2;
114 drop procedure p1;