It does not work to write a trigger that creates a new table with a name containing the row identifier of the original table.

Simply put, there is a table of owners with a primary key owner_id . When a new row is inserted into this table, a new table should be created, in the name of which this same owner_id will be present.

 CREATE TRIGGER `owners_after_insert` AFTER INSERT ON `owners` FOR EACH ROW BEGIN CREATE TABLE IF NOT EXISTS CONCAT('`owner_', NEW.owner_id, '_members`') ( `user_id` BIGINT UNSIGNED NOT NULL , PRIMARY KEY(`user_id`) ) END

In my version the muscle swears on CONCAT . How to get around this and implement the question in question?

    2 answers 2

    I found the answer to en.so with a general conclusion about the impossibility of such an operation:

    As far as I am aware, creating a table inside a trigger is not possible. See here:

    http://forums.mysql.com/read.php?99,121849,122609#msg-122609

    Restrictions for Stored Routines, Triggers, and Events page.

      Try this:

       CREATE trigger [dbo].[TR_CreateTableByOwnerID] on [dbo].[owners] after insert as begin --Инициализация declare @owner_ID int declare @querySQL nvarchar(255) --Получение ownerID из вставленной строки select @owner_ID =ownerID from inserted --Конструирование запроса на создание таблицы set @querySQL='CREATE TABLE dbo.Table_'+cast(@owner_ID as varchar)+' ( id int identity, Field1 varchar, Fiel2 int...)' --Выполнения запроса из строки exec sp_executesql @querySQL end
      • Thanks for the answer. You have proposed a strange syntax. Perhaps I did not quite understand anything. The muscle (I have version 5.7) trigger_time ( AFTER ) and trigger_event ( INSERT ) go before ON , and FOR EACH ROW required. It does not work unfortunately. - alexis031182
      • Unfortunately, I realized that in the MySQL syntax it is not strong ((But I googled and understood that the same logic that I have can be applied. Instead of the querySQL variable, you should declare "PREPARE querySQL FROM" CREATE TABLE IF NOT EXISTS ... " You can get an ID, as I understand it, with the NEW.ownerID command. By collecting a string, you can execute it as a query with the EXECUTE querySQL command. Instead of your string "CONCAT (' owner_', NEW.owner_id, '_members ')" use "owner_? , and at the time of execution, pass the parameter with the command USING (there will be something like EXECUTE querySQL USING @ownerID). - HoloD
      • This is probably all workable (including with so-called Prepared Statements requests), but not in the muscle, I tried. Prepared queries in MySQL allow you to create tables, but not in the body of a trigger. Such is the limitation of the DBMS itself (the link to the official documentation describing the limitations I have given in the answer). In fact, there is no solution. It may be that some hack is at your own peril and risk, but then the game will not be worth the candle, since what could not be processed through a trigger can be solved without any problems using programming language tools. Selyavi. - alexis031182
      • Another problem can be solved by calling a stored procedure from the trigger that creates the tables (if this is not subject to restrictions). - HoloD
      • I'm not sure, but most likely also falls. - alexis031182