summaryrefslogtreecommitdiff
path: root/mysql-test/main/trigger_wl3253.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/trigger_wl3253.result')
-rw-r--r--mysql-test/main/trigger_wl3253.result502
1 files changed, 502 insertions, 0 deletions
diff --git a/mysql-test/main/trigger_wl3253.result b/mysql-test/main/trigger_wl3253.result
new file mode 100644
index 00000000000..bd2d250cb2f
--- /dev/null
+++ b/mysql-test/main/trigger_wl3253.result
@@ -0,0 +1,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.
+#
View Lorry Controller Status