「ERROR 1435 – Trigger in wrong schema」

内容简介

处理ERROR 1435 (HY000): Trigger in wrong schema错误。

问题描述

在执行REANME TABLE a TO b时，产生ERROR 1435 (HY000): Trigger in wrong schema错误。

问题原因

正如「13.1.33 RENAME TABLE Syntax」文档所述：

If a table has triggers, attempts to rename the table into a different database fail with a Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA) error.

所以，无法重命名带有触发器的表，这些表要单独处理。

解决办法

单独处理这些触发器： 

SELECT
	CONCAT(
		"CREATE TRIGGER ",
		TRIGGER_NAME,
		" ",
		ACTION_TIMING,
		" ",
		EVENT_MANIPULATION,
		" ON ",
		EVENT_OBJECT_SCHEMA,
		".",
		EVENT_OBJECT_TABLE,
		" FOR EACH ROW ",
		ACTION_STATEMENT
	) TRIGGER_SCHEMA,
	TRIGGER_NAME,
	ACTION_STATEMENT
FROM
	information_schema. TRIGGERS;

参考文献

MySQL 5.6 Reference Manual/13.1.32 RENAME TABLE Syntax
MariaDB/RENAME TABLE
How do you list all triggers in a MySQL database?
Create Trigger in MySQL

Filed under: K4NZDROID - @ 9:34 PM