MySQL Trigger with dynamic table name
- by Thomas
I've look around a bit and can't quite find an answer to my problem:
I want a trigger to execute after an insert on a table and to take that data that is being inserted and do two things
Create a new table from the client id and partner id
Insert the 'data' that just was inserted into the new table
I am fairly new to the Stored procedures and triggers so I came up with this but am having difficulty debugging it:
delimiter $$
CREATE TRIGGER trg_creas_insert BEFORE INSERT ON tracking.creas for each row
BEGIN
DECLARE @tableName varchar(40);
DECLARE @createStmnt mediumtext;
SET @tableName = concat('crea_','_', NEW.idClient_crea,'_',NEW.idPartenaire_crea);
SET @createStmnt = concat('CREATE TABLE IF NOT EXISTS', @tableName, '( `data_crea` mediumtext NOT NULL ) ENGINE=MyISAM AUTO_INCREMENT=29483330 DEFAULT CHARSET=utf8 PACK_KEYS=0');
PREPARE stmt FROM @createStmnt;
EXECUTE stmt;
INSERT INTO @tableName (data_crea) values (NEW.data_crea);
END$$
delimiter ;
Thoughts?
