EVOLUTION-MANAGER

Edit File: trigger_wl3253.result

#
# 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.
#