MySQL触发器在多个数据库中插入

Question

我有一个名为catalog DB的数据库，其中包含4个表，其中一个是Customers表，其中包含客户及其别名列表。我想在所有客户数据库中可用的表(tableA)中插入一行。这必须在目录DB中的表(table_in_catalogDB)中的INSERT操作中发生。每个客户都有相同的一组表，这些表有他们自己的数据。

CREATE TRIGGER catalogDB.insertInMultipleDatabases AFTER INSERT ON catalogDB.table_in_catalogDB
FOR EACH ROW
BEGIN
DECLARE maxCustId INT;
DECLARE minCustId INT;
DECLARE loopCounter INT; // For looping over all the customers
DECLARE cust_name VARCHAR(100);
SELECT min(custId) AS minCustId, max(custId) AS maxCustId FROM Customers;
SET loopCounter = minCustId;
WHILE loopCounter <= maxCustId DO // Using loopCounter here as the custId's are 
SET cust_name = (SELECT alias FROM Customers WHERE custId = loopCounter);
SET dbName = CONCAT(cust_name, '.tableA'); //tableA is available for all customers
INSERT INTO dbName (column names) VALUES (from new.column); // This query has to be dynamic because alias name is concatenated with the table name and then to be used in the INSERT statement
SET loopCounter = loopCounter + 1;
END WHILE;
END

我尝试在此触发器中使用游标和预处理语句，然后了解到触发器不能包含预处理语句。我需要使用触发器。

Answer 1

不能在触发器中使用动态SQL。

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html表示:

预处理语句的SQL语法可以在存储过程中使用，但不能在存储函数或触发器中使用。

但是你不能插入到一个以变量命名的表中。变量只能用来代替值，而不能代替标识符。在解析SQL语句时，必须固定SQL语句中的所有标识符。对于触发器中的非动态语句，这意味着在创建触发器时必须固定标识符。

所以你唯一的选择是硬编码你所有的客户数据库:

CREATE TRIGGER catalogDB.insertInMultipleDatabases AFTER INSERT ON catalogDB.table_in_catalogDB
FOR EACH ROW
BEGIN
INSERT INTO customer1.tableA (column names) VALUES (NEW.column);
INSERT INTO customer2.tableA (column names) VALUES (NEW.column);
INSERT INTO customer3.tableA (column names) VALUES (NEW.column);
INSERT INTO customer4.tableA (column names) VALUES (NEW.column);
INSERT INTO customer5.tableA (column names) VALUES (NEW.column);
...continue with one insert for each customer...
END

这意味着如果您添加一个客户，那么您需要用多一个INSERT行重新定义触发器。

对不起，在这种情况下，你只能用触发器做这些。

我不会选择使用触发器。我会在客户端应用程序中编写代码来遍历客户数据库。

我需要使用触发器。

对不起，除非硬编码所有客户数据库名称，否则不能使用触发器。