Mysql – SQL Trigger that creates new table when a new record is added to current table

ms accessMySQLmysql-workbenchtable

It's been quite a few years since I have done DB work. I am working through mysql workbench (using MS Access as a frontend).

I have a base table that I am multilinking (basically a name index type of thing). Through a form, the table is updated (a record added) and I want that new record to trigger an event that creates a table and that table is named based on what the user inputted in the form/record.

Any thoughts on how to do this? Good tutorial/documentation that you would recommend reading?
Thanks!

Best Answer

First things first

this would you use, to create special table per id Name and table you have to add

DELIMITER $$

CREATE TRIGGER after_table_insert
AFTER INSERT
ON tabl1 FOR EACH ROW
BEGIN
    SET @sql = CONCAR("CREATE TABLE table_",NEW.name,"_",NEW.ID, " (id INT,name TEXT)");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$

DELIMITER ;

If you had a table for all

CREATE TABLE Table1_companion
( id BIGINT AUTO_INCREMENT PRIMARY KEy
, Refid  INTEGER
,col1 TEXT
,col2 TEXT
,FOREIGN KEY (Refid) REFERENCES tavle1(id));

So you have 

SELECT t1.*,t2.* FROM table1 t1 INNER JOIN Table1_companion t2 ON t1.id = t2.Refid;

And when you have a n:m Relationship

You need two tables

CREATE TABLE Table1_companion
( tc_id BIGINT AUTO_INCREMENT PRIMARY KEy
,col1 TEXT
,col2 TEXT);

CREATE TABLE Table1_relation
(
, Ref_id  INTEGER
, Ref_tc_id   BIGINT
,FOREIGN KEY (Ref_id) REFERENCES table1(id)
,FOREIGN KEY (Ref_tc_id) REFERENCES Table1_companion(tc_id ));

Of course you need two joins for that.

You could take a look at normalization.

With that you have a relational database, that works with every rdms.

this is of course only a basic idea, you must have your own datatypes and foreign keys need of both sides the same datatype, and always remember have every information only once in your database

Related Question