106
106
DROP USER mysqltest_dfn@localhost;
107
107
DROP USER mysqltest_inv@localhost;
108
108
DROP DATABASE mysqltest_db1;
113
--echo # Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
115
let $MYSQLD_DATADIR=`SELECT @@datadir`;
118
DROP TABLE IF EXISTS t1, t2, t3;
121
CREATE TABLE t1 ( a INT );
122
CREATE TABLE t2 ( a INT );
123
CREATE TABLE t3 ( a INT );
124
INSERT INTO t1 VALUES (1), (2), (3);
125
INSERT INTO t2 VALUES (1), (2), (3);
126
INSERT INTO t3 VALUES (1), (2), (3);
128
--echo # We simulate importing a trigger from 5.0 by writing a .TRN file for
129
--echo # each trigger plus a .TRG file the way MySQL 5.0 would have done it,
130
--echo # with syntax allowed in 5.0 only.
132
--echo # Note that in 5.0 the following lines are missing from t1.TRG:
134
--echo # client_cs_names='latin1'
135
--echo # connection_cl_names='latin1_swedish_ci'
136
--echo # db_cl_names='latin1_swedish_ci'
138
--write_file $MYSQLD_DATADIR/test/tr11.TRN
143
--write_file $MYSQLD_DATADIR/test/tr12.TRN
148
--write_file $MYSQLD_DATADIR/test/tr13.TRN
153
--write_file $MYSQLD_DATADIR/test/tr14.TRN
158
--write_file $MYSQLD_DATADIR/test/tr15.TRN
163
--write_file $MYSQLD_DATADIR/test/t1.TRG
165
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM non_existing_table' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a'
167
definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost'
170
--write_file $MYSQLD_DATADIR/test/t2.TRG
172
triggers='Not allowed syntax here, and trigger name cant be extracted either.'
174
definers='root@localhost'
177
--echo # We will get parse errors for most DDL and DML statements when the table
178
--echo # has broken triggers. The parse error refers to the first broken
180
--error ER_PARSE_ERROR
181
CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1);
182
--error ER_PARSE_ERROR
183
CREATE TRIGGER tr22 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM non_existing_table;
185
--error ER_PARSE_ERROR
186
INSERT INTO t1 VALUES (1);
187
--error ER_PARSE_ERROR
188
INSERT INTO t2 VALUES (1);
189
--error ER_PARSE_ERROR
191
--error ER_PARSE_ERROR
192
UPDATE t1 SET a = 1 WHERE a = 1;
194
--error ER_PARSE_ERROR
195
RENAME TABLE t1 TO t1_2;
206
--echo # Make sure there is no trigger file left.
207
--list_files $MYSQLD_DATADIR/test/ tr*
209
--echo # We write the same trigger files one more time to test DROP TABLE.
210
--write_file $MYSQLD_DATADIR/test/tr11.TRN
215
--write_file $MYSQLD_DATADIR/test/tr12.TRN
220
--write_file $MYSQLD_DATADIR/test/tr13.TRN
225
--write_file $MYSQLD_DATADIR/test/tr14.TRN
230
--write_file $MYSQLD_DATADIR/test/tr15.TRN
235
--write_file $MYSQLD_DATADIR/test/t1.TRG
237
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM non_existing_table' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a'
239
definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost'
246
--echo # Make sure there is no trigger file left.
248
--list_files $MYSQLD_DATADIR/test/ tr*
250
CREATE TABLE t1 ( a INT );
251
CREATE TABLE t2 ( a INT );
252
INSERT INTO t1 VALUES (1), (2), (3);
253
INSERT INTO t2 VALUES (1), (2), (3);
255
--echo # We write three trigger files. First trigger is syntaxically incorrect, next trigger is correct
256
--echo # and last trigger is broken.
257
--echo # Next we try to execute SHOW CREATE TRGGIR command for broken trigger and then try to drop one.
258
--write_file $MYSQLD_DATADIR/test/tr11.TRN
263
--write_file $MYSQLD_DATADIR/test/tr12.TRN
268
--write_file $MYSQLD_DATADIR/test/t1.TRG
270
triggers='CREATE the wrongest trigger_in_the_world' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2'
272
definers='root@localhost' 'root@localhost' 'root@localhost'
277
SHOW CREATE TRIGGER tr12;
278
SHOW CREATE TRIGGER tr11;
added
removed
mysql-test/t/trigger-compat.test
