1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
|
#
# WL#3253: multiple triggers per table
#
SET @binlog_format_saved = @@binlog_format;
SET binlog_format=ROW;
SET time_zone='+00:00';
#
# Test 1.
# Check that the sequence of triggers for the same combination
# of event type/action type can be created for a table
# and is fired consequently in the order of its creation
# during statement execution.
# In this test we check BEFORE triggers.
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
INSERT INTO t1 VALUES (1);
SELECT * FROM t2 ORDER BY b;
a b
1 1
101 2
DROP TABLE t2;
DROP TABLE t1;
#
# Test 2.
# Check that the sequence of triggers for the same combination
# of event type/action type can be created for a table
# and is fired consequently in the order of its creation
# during statement execution.
# In this test we check AFTER triggers.
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
CREATE TRIGGER tr1_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
CREATE TRIGGER tr2_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
INSERT INTO t1 VALUES (1);
SELECT * FROM t2 ORDER BY b;
a b
1 1
101 2
DROP TABLE t2;
DROP TABLE t1;
#
# Test 3.
# Check that the sequences of triggers for the different event types
# can be created for a table and are fired consequently
# in the order of its creation during statement execution.
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300);
INSERT INTO t1 VALUES (1);
SELECT * FROM t2 ORDER BY b;
a b
1 1
101 2
UPDATE t1 SET a = 5;
SELECT * FROM t2 ORDER BY b;
a b
1 1
101 2
5 3
305 4
DROP TABLE t2;
DROP TABLE t1;
#
# Test 4.
# Check that every new created trigger has unique action_order value
# started from 1 and NOT NULL value for creation timestamp.
#
CREATE TABLE t1 (a INT);
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00');
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name created action_order
tr1_bi 2013-01-31 09:00:00.00 1
tr2_bi 2013-01-31 09:00:01.00 2
DROP TABLE t1;
SET TIMESTAMP=DEFAULT;
#
# Test 5.
# Check that action_order attribute isn't shown
# in the output of SHOW TRIGGERS and SHOW CREATE TRIGGER
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
SHOW TRIGGERS;
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
tr1_bi INSERT t1 SET @a:=1 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
SHOW CREATE TRIGGER tr1_bi;
Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
tr1_bi STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 latin1 latin1_swedish_ci latin1_swedish_ci #
DROP TABLE t1;
#
# Test 6.
# Check that action_order attribute is reused when trigger
# are recreated.
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
DROP TRIGGER tr1_bi;
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
DROP TABLE t1;
#
# Test 7.
# Check that it is possible to create several triggers with
# the same value for creation timestamp.
#
CREATE TABLE t1 (a INT);
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name created action_order
tr1_bi 2013-01-31 09:00:01.00 1
tr2_bi 2013-01-31 09:00:01.00 2
DROP TABLE t1;
SET TIMESTAMP=DEFAULT;
#
# Test 8.
# Check that SHOW CREATE TRIGGER outputs the CREATED attribute
# and it is not NULL
#
CREATE TABLE t1 (a INT);
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
SHOW CREATE TRIGGER tr1_bi;
Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
tr1_bi STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 latin1 latin1_swedish_ci latin1_swedish_ci 2013-01-31 09:00:01.00
DROP TABLE t1;
SET TIMESTAMP=DEFAULT;
#
# Test 9.
# Check that SHOW TRIGGERS outputs the CREATED attribute
# and it is not NULL.
#
CREATE TABLE t1 (a INT);
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
SHOW TRIGGERS;
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
tr1_bi INSERT t1 SET @a:=1 BEFORE 2013-01-31 09:00:01.00 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW 2013-01-31 09:00:01.00 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
DROP TABLE t1;
SET TIMESTAMP=DEFAULT;
#
# Test 10.
# Check that FOLLOWS clause is supported and works correctly.
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300);
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi INSERT INTO t2 (a) VALUES (NEW.a + 200);
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
tr2_bi 2
tr3_bi 3
INSERT INTO t1 VALUES (1);
SELECT * FROM t2 ORDER BY b;
a b
101 1
201 2
301 3
DROP TABLE t2;
DROP TABLE t1;
#
# Test 11.
# Check that PRECEDES clause is supported and works correctly.
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300);
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr3_bi INSERT INTO t2 (a) VALUES (NEW.a + 200);
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
tr2_bi 2
tr3_bi 3
INSERT INTO t1 VALUES (1);
SELECT * FROM t2 ORDER BY b;
a b
101 1
201 2
301 3
DROP TABLE t2;
DROP TABLE t1;
#
# Test 12.
# Check that the PRECEDES works properly for the 1st trigger in the chain.
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi INSERT INTO t2 (a) VALUES (NEW.a);
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr0_bi 1
tr1_bi 2
INSERT INTO t1 VALUES (1);
SELECT * FROM t2 ORDER BY b;
a b
1 1
101 2
DROP TABLE t2;
DROP TABLE t1;
#
# Test 13.
# Check that error is reported if the FOLLOWS clause references to
# non-existing trigger
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr0_bi SET @a:=2;
ERROR HY000: Referenced trigger 'tr0_bi' for the given action time and event type does not exist
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
tr3_bi 2
DROP TABLE t1;
#
# Test 14.
# Check that error is reported if the PRECEDES clause references to
# non-existing trigger
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr0_bi SET @a:=2;
ERROR HY000: Referenced trigger 'tr0_bi' for the given action time and event type does not exist
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
tr3_bi 2
DROP TABLE t1;
#
# Test 15.
# Check that action_order value is independent for each type of event
# (INSERT/UPDATE/DELETE)
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
tr2_bi 2
tr1_bu 1
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr2_bi SET @a:=3;
CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3;
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name action_order
tr1_bi 1
tr2_bi 2
tr3_bi 3
tr1_bu 1
tr2_bu 2
DROP TABLE t1;
#
# Test 16.
# Check that the trigger in the clause FOLLOWS/PRECEDES can refences
# only to the trigger for the same ACTION/TIMINMG
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3;
ERROR HY000: Referenced trigger 'tr1_bi' for the given action time and event type does not exist
CREATE TRIGGER tr2_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3;
ERROR HY000: Referenced trigger 'tr1_bi' for the given action time and event type does not exist
CREATE TRIGGER tr1_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3;
ERROR HY000: Referenced trigger 'tr1_bu' for the given action time and event type does not exist
CREATE TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3;
ERROR HY000: Referenced trigger 'tr1_bi' for the given action time and event type does not exist
SHOW TRIGGERS;
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
tr1_bi INSERT t1 SET @a:=1 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
tr1_bu UPDATE t1 SET @a:=3 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
def test tr1_bu UPDATE def test t1 1 NULL SET @a:=3 ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
DROP TABLE t1;
#
# Test 17. Check that table's triggers are dumped correctly.
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
DROP TABLE t1;
#
# Test 18. Check that table's triggers are dumped in right order
# taking into account the PRECEDES/FOLLOWS clauses.
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a:=0;
CREATE TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=0;
# Expected order of triggers in the dump is: tr0_bi, tr1_bi, tr1_1_bi, tr2_i.
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
DROP TABLE t1;
#
# Test 19. Check that table's triggers are dumped correctly in xml.
#
CREATE TABLE t1 (a INT);
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00');
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
SET TIMESTAMP=DEFAULT;
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
<table_data name="t1">
</table_data>
<triggers name="t1">
<trigger Trigger="tr1_bi" sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="2013-01-31 09:00:00.00">
<![CDATA[
CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1
]]>
</trigger>
<trigger Trigger="tr2_bi" sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="2013-01-31 09:00:00.00">
<![CDATA[
CREATE DEFINER=`root`@`localhost` TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2
]]>
</trigger>
<trigger Trigger="tr1_bu" sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="2013-01-31 09:00:00.00">
<![CDATA[
CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3
]]>
</trigger>
</triggers>
</database>
</mysqldump>
DROP TABLE t1;
#
# Test 20. Check that the statement CHECK TABLE FOR UPGRADE outputs
# the warnings for triggers created by a server without support for wl3253.
#
CREATE TABLE t1 (a INT);
FLUSH TABLE t1;
CHECK TABLE t1 FOR UPGRADE;
Table Op Msg_type Msg_text
test.t1 check status OK
SHOW TRIGGERS;
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
tr1_bi INSERT t1 SET @a:=1 BEFORE NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
tr1_ai INSERT t1 SET @a:=2 AFTER NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
def test tr1_ai INSERT def test t1 1 NULL SET @a:=2 ROW AFTER NULL NULL OLD NEW NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
SHOW CREATE TRIGGER tr1_bi;
Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
tr1_bi NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 latin1 latin1_swedish_ci latin1_swedish_ci NULL
SHOW CREATE TRIGGER tr1_ai;
Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
tr1_ai NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW SET @a:=2 latin1 latin1_swedish_ci latin1_swedish_ci NULL
DROP TABLE t1;
SET binlog_format=@binlog_format_saved;
# End of tests.
#
|
