MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
trig_frkey2.inc
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