MySQL trigger to INSERT in multiple Databases-CodePudding

I have a DB named catalog DB which contains 4 tables in that one is the Customers table which has a list of customers and their alias. I want to insert a row in a table (tableA) available in all customer Databases. This has to happen on INSERT in a table (table_in_catalogDB) in catalog DB. Each customer has the same set of tables which has their own data.

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

I tried using the cursor and prepared statement in this trigger and then understood that triggers cannot contain prepared statements. I need to use triggers.

CodePudding user response：

You can't use dynamic SQL in a trigger.

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

SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers.

But you can't insert into a table named in a variable. Variables can be used only in place of a value, not an identifier. All identifiers must be fixed in the SQL statement at the time the statement is parsed. For non-dynamic statements in a trigger, that means the identifiers must be fixed at the time the trigger is created.

So your only option is to hard-code all your customer databases:

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

This means if you add a customer, then you need to redefine the trigger with one more INSERT line.

Sorry, that's all you can do with a trigger in this case.

I wouldn't choose to use a trigger for this. I would write code in the client application to loop over the customer databases.

I need to use triggers.

Sorry, you can't use triggers for this unless you hard-code all the customer database names.