MySQL #1422 Explicit or implecit commit is not allowed in stored function or trigger

Question

I try to automate the copy-row from a collection-table to a dedicated table. Sometimes the dedicated table does not exist, therefor I check for existance, and if needed I create the table before copying the row. This code is included in a trigger in MariaDB

From the latest inserted row in the table "allwelds" I want to know the content of the column ProjectName, the complete row have to be inserted in the table with the name of the ProjectName First I check the existance of the table. If the table does not exist (select returns 0 ), I create the table with the name of the variable "qname" and insert the complete row in it. If the table exist (select returns 1 ) do the insert of the row in the table with the name of the variable "qname"

The following code results in the following error:
MySQL #1422 Explicit or implecit commit is not allowed in stored function or trigger

BEGIN
DECLARE qname Varchar(24) DEFAULT "EMPTY";
DECLARE qid INT DEFAULT 0;
DECLARE table_exist INT DEFAULT 5;           

SELECT id, ProjectName INTO qid, qname FROM allwelds WHERE id = (SELECT MAX(id) FROM allwelds );

SELECT count(*) into table_exist FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'ugm') AND (TABLE_NAME = qname );
CASE table_exist

WHEN 0 THEN
    CREATE TABLE qname ( id int, Datum date, Tijd time, ProjectName Varchar(24), ...........etc, etc, ....................... ); 
    INSERT INTO qname ( id, Datum, Tijd, ProjectName, , ...........etc, etc, ....................... )
        SELECT id, date(timestamp), time(timestamp), , ...........etc, etc, ....................... );   FROM `allwelds` WHERE id = qid;

WHEN 1 THEN 
    INSERT INTO qname ( id, Datum, Tijd, ProjectName, , ...........etc, etc, ....................... )
    SELECT id, date(timestamp), time(timestamp), , ...........etc, etc, ....................... );   FROM `allwelds` WHERE id = qid;

END CASE;
END

Can anyone help me with this code?

Answer 1

There is another problem waiting to be discovered. qname is dynamic, yet you have INSERT INTO qname ..., which is using "qname" as a static table name. The obvious solution is CONCAT, PREPARE, EXECUTE, etc. But, let's not go that way.

Do not create lots of tables with the same schema. This is almost always a bad design and a maintenance nightmare, and sometimes a performance problem.

Instead, have an extra column in a single table for throwing the stuff into. And put qname in that column. Probably the first part of the composite PRIMARY KEY for that table would be qname.

Another problem... Don't discover the columns via a SELECT and MAX(id). Instead, use the pseudo table OLD (there is also NEW). It is a lot simpler and faster. Plus it avoids race conditions where MAX(id) could grab the id from a different connection !

Bottom line: Take the time to study TRIGGERs and look at examples.