MySQL Trigger with dynamic table name

Posted by Thomas on Stack Overflow See other posts from Stack Overflow or by Thomas
Published on 2010-03-24T18:55:47Z Indexed on 2010/03/24 19:13 UTC
Read the original article Hit count: 273

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

  1. Create a new table from the client id and partner id
  2. 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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about trigger