Use of table name variable in Mysql trigger-CodePudding

I'm stuck with this horrible solution when I have a main table named buffer and > 100 tables with similar names 'ftm', 'ada', ... etc and I need a trigger on the main table that inserts data into these secondary tables depending on the table name in destination_table field:

CREATE TRIGGER distribute AFTER INSERT ON buffer
  FOR EACH ROW BEGIN
    CASE 
      WHEN NEW.destination_table = 'ftm' THEN INSERT INTO ftm (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
      WHEN NEW.destination_table = 'sol' THEN INSERT INTO sol (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
      WHEN NEW.destination_table = 'ada' THEN INSERT INTO ada (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
      WHEN NEW.destination_table = 'ltc' THEN INSERT INTO ltc (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
      WHEN NEW.destination_table = 'mkr' THEN INSERT INTO mkr (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
      WHEN ... > 100 similar conditions ...
    END CASE;
  END;

I have tried a couple of ways to overcome this:

DECLARE x VARCHAR(255);
SET x = NEW.destination_table;
INSERT INTO x (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);

DECLARE x VARCHAR(255);
SET x = (SELECT destination_table FROM collection_1m LIMIT 1);
INSERT INTO x (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);

but all it does is throwing an error like THE TABLE 'schema.x' does not exist... i.e., it doesn't want to read the value stored in that 'x' variable (if anything at all stored there).

Any suggestions to make this piece of code smarter? Appreciate any help!

-------UPDATE---------- I've tried to do it like this:

DELIMITER //
            
CREATE TABLE dist ( id SMALLINT NOT NULL AUTO_INCREMENT,
  dest_table varchar(32) NOT NULL,
  param1 BIGINT NOT NULL,
  param2 FLOAT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
            
CREATE TABLE aaa ( id SMALLINT NOT NULL AUTO_INCREMENT,
  param1 BIGINT NOT NULL,
  param2 FLOAT NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB;
            
CREATE PROCEDURE dispatch (IN dest_table CHAR(32), IN val_a BIGINT, IN val_b FLOAT)
  BEGIN
    SET @table = dest_table;
    SET @param1 = val_a;
    SET @param2 = val_b;
    SET @s = CONCAT('INSERT INTO ', @table, '( param1, param2) VALUES (',@param1,',',@param2,')');
                
    PREPARE stmt3 FROM @s;
    EXECUTE stmt3;
                        
  END;

CREATE TRIGGER distr AFTER INSERT ON dist 
  FOR EACH ROW
    BEGIN                
      CALL dispatch( NEW.dest_table, NEW.param1, NEW.param2 );
    END;            
  END; //
  DELIMITER ;

But now it says ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

CodePudding user response：

It is correct that you can't start a new prepared statement in a trigger. That includes any routine called from the trigger. It's not a syntax limitation, it's a problem that there can only be one prepared statement at a time in a MySQL thread, and the INSERT that spawned the trigger may itself be a prepared statement. This is a design decision in the implementation of MySQL Server.

You can shorten your CASE statement format a little bit, because all the conditions are comparing NEW.destination_table to a fixed value:

CASE NEW.destination_table
  WHEN 'ftm' THEN INSERT INTO ftm (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
  WHEN 'sol' THEN INSERT INTO sol (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
  WHEN 'ada' THEN INSERT INTO ada (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
  WHEN 'ltc' THEN INSERT INTO ltc (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
  WHEN 'mkr' THEN INSERT INTO mkr (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed);
  WHEN ... > 100 similar conditions ...
END CASE;

Other than that, the only alternative would be to forego the trigger and do the secondary INSERT from application code.

CodePudding user response：

CREATE TRIGGER distribute AFTER INSERT ON buffer FOR EACH ROW BEGIN

  IF NEW.destination_table = 'ftm' THEN INSERT INTO ftm (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed); END IF;

  IF NEW.destination_table = 'sol' THEN INSERT INTO sol (opened, open, high, low, close, volume, closed) VALUES (NEW.opened, NEW.open, NEW.high, NEW.low, NEW.close, NEW.volume, NEW.closed); END IF;

100 Similar IF statements

END;