Debian Codegraph @

mysql-test/suite/funcs_1/triggers/trig_frkey2.inc

raw
1 #################################################################
2 # This file inclde tests that address the foreign key cases of
3 # the following requirements since they are specific to innodb.
4 # Other test cases for these requirements are included in the
5 # triggers_master.test file.
6 #################################################################
7
8 --disable_abort_on_error
9
10 # OBN - The following tests are disabled until triggers are supported with forign
11 # keys in innodb (foreign keys tests dispabled - bug 11472)
12 #################################################################################
13 #Section x.x.x.3
14 # Test case: Similar to 3.5.10.5 but with ten tables to see if multiple triggers
15 # can be executed at once
16 let $message= Testcase x.x.x.3:;
17 --source include/show_msg.inc
18
19 --disable_warnings
20 DROP TABLE IF EXISTS t1, t2;
21 --enable_warnings
22
23 eval CREATE TABLE t0 (col1 char(50)) ENGINE=$engine_type;
24 eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50),
25 PRIMARY KEY (id)) ENGINE=$engine_type;
26 eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
27 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
28 ON DELETE SET NULL) ENGINE=$engine_type;
29 eval CREATE TABLE t3 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
30 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
31 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
32 eval CREATE TABLE t4 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
33 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
34 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
35 eval CREATE TABLE t5 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
36 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
37 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
38 eval CREATE TABLE t6 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
39 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
40 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
41 eval CREATE TABLE t7 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
42 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
43 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
44 eval CREATE TABLE t8 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
45 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
46 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
47 eval CREATE TABLE t9 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
48 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
49 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
50 eval CREATE TABLE t10(id INT PRIMARY KEY, f_id INT, INDEX par_ind
51 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
52 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
53 eval CREATE TABLE t11(id INT PRIMARY KEY, f_id INT, INDEX par_ind
54 (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
55 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
56
57 create trigger tr1 after update on t2 for each row
58 insert into t0 values ('tr_t2');
59 create trigger tr2 after update on t3 for each row
60 insert into t0 values ('tr_t3');
61 create trigger tr3 after update on t4 for each row
62 insert into t0 values ('tr_t4');
63 create trigger tr3 after update on t5 for each row
64 insert into t0 values ('tr_t5');
65 create trigger tr4 after update on t6 for each row
66 insert into t0 values ('tr_t6');
67 create trigger tr5 after update on t7 for each row
68 insert into t0 values ('tr_t7');
69 create trigger tr5 after update on t8 for each row
70 insert into t0 values ('tr_t8');
71 create trigger tr6 after update on t9 for each row
72 insert into t0 values ('tr_t9');
73 create trigger tr7 after update on t10 for each row
74 insert into t0 values ('tr_t10');
75 create trigger tr8 after update on t11 for each row
76 insert into t0 values ('tr_t11');
77
78 insert into t1 values (1,'Department A');
79 insert into t1 values (2,'Department B');
80 insert into t1 values (3,'Department C');
81
82 insert into t2 values (1,2,'Employee');
83 insert into t3 values (1,2,'Employee');
84 insert into t4 values (1,2,'Employee');
85 insert into t5 values (1,2,'Employee');
86 insert into t6 values (1,2,'Employee');
87 insert into t7 values (1,2,'Employee');
88 insert into t8 values (1,2,'Employee');
89 insert into t9 values (1,2,'Employee');
90 insert into t10 values (1,2,'Employee');
91 insert into t11 values (1,2,'Employee');
92
93 select * from t1;
94 select * from t2;
95 select * from t3;
96 select * from t4;
97 select * from t5;
98 select * from t6;
99 select * from t7;
100 select * from t8;
101 select * from t9;
102 select * from t10;
103 select * from t11;
104
105 delete from t1 where id=2;
106 select * from t1;
107 select * from t2;
108 select * from t3;
109 select * from t4;
110 select * from t5;
111 select * from t6;
112 select * from t7;
113 select * from t8;
114 select * from t9;
115 select * from t10;
116 select * from t11;
117
118 select * from t0;
119
120 # Cleanup
121 drop trigger tr1;
122 drop trigger tr2;
123 drop trigger tr3;
124 drop trigger tr4;
125 drop trigger tr5;
126 drop trigger tr6;
127 drop trigger tr7;
128 drop trigger tr8;
129 drop trigger tr9;
130 drop trigger tr10;
131 drop table t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t1,t0;
132
133
134
135
136 #Section 3.5.10.5
137 # Test case: Ensure that every trigger that should be activated by every possible
138 # type of implicit update of its subject table (e.g. a FOREIGN KEY SET
139 # DEFAULT action or an UPDATE of a view based on the subject table)
140 # is indeed activated correctly.
141 let $message= Testcase 3.5.10.5 (foreign keys):;
142 --source include/show_msg.inc
143
144
145 --disable_warnings
146 DROP TABLE IF EXISTS t1, t2;
147 --enable_warnings
148
149 eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50),
150 PRIMARY KEY (id)) ENGINE=$engine_type;
151 eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
152 INDEX par_ind (f_id), col1 char(50),
153 FOREIGN KEY (f_id) REFERENCES t1(id)
154 ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
155 create trigger tr_t2 after update on t2
156 for each row set @counter=@counter+1;
157
158 insert into t1 values (1,'Department A');
159 insert into t1 values (2,'Department B');
160 insert into t1 values (3,'Department C');
161 insert into t2 values (1,2,'Emp 1');
162 insert into t2 values (2,2,'Emp 2');
163 insert into t2 values (3,2,'Emp 3');
164 insert into t2 values (4,2,'Emp 4');
165 insert into t2 values (5,2,'Emp 5');
166 insert into t2 values (6,3,'Emp 6');
167 set @counter=0;
168
169 select * from t1;
170 select * from t2;
171 select @counter;
172
173 update t1 set id=4 where id=3;
174 select * from t1;
175 select * from t2;
176 select @counter;
177
178 delete from t1 where id=2;
179 select * from t1;
180 select * from t2;
181 select @counter;
182
183 # This is to verify that the trigger works when updated directly
184 update t2 set col1='Emp 5a' where id=5;
185 select * from t2;
186 select @counter;
187
188 # Cleanup
189 drop trigger tr_t2;
190 drop table t2, t1;
191
192
193 #Section 3.5.10.6
194 # Test case: Ensure that every trigger that should be activated by every possible
195 # type of implicit deletion from its subject table (e.g. a FOREIGN KEY
196 # CASCADE action or a DELETE from a view based on the subject table)
197 # is indeed activated correctly.
198 let $message= Testcase 3.5.10.6 (foreign keys):;
199 --source include/show_msg.inc
200
201 --disable_warnings
202 DROP TABLE IF EXISTS t1, t2;
203 --enable_warnings
204
205 eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50),
206 PRIMARY KEY (id)) ENGINE=$engine_type;
207 eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
208 INDEX par_ind (f_id), col1 char(50),
209 FOREIGN KEY (f_id) REFERENCES t1(id)
210 ON DELETE CASCADE) ENGINE=$engine_type;
211
212 create trigger tr_t2 before delete on t2
213 for each row set @counter=@counter+1;
214
215 insert into t1 values (1,'Department A');
216 insert into t1 values (2,'Department B');
217 insert into t1 values (3,'Department C');
218 insert into t2 values (1,2,'Emp 1');
219 insert into t2 values (2,2,'Emp 2');
220 insert into t2 values (3,2,'Emp 3');
221 insert into t2 values (4,2,'Emp 4');
222 insert into t2 values (5,2,'Emp 5');
223 insert into t2 values (6,3,'Emp 6');
224 set @counter=0;
225
226 select * from t1;
227 select * from t2;
228 select @counter;
229
230 delete from t1 where id=2;
231
232 select * from t1;
233 select * from t2;
234 select @counter;
235
236 # This is to verify that the trigger works when deleted directly
237 delete from t2 where id=6;
238 select * from t2;
239 select @counter;
240
241 # Cleanup
242 drop trigger tr_t2;
243 drop table t2, t1;
244