CODEWITHSUNDEEP
mysqlsql
user5181421
user5181421

Reputation:

SQL Trigger Create Table statement

I want to create a new table if someone inserts a value to the users table. I have a trigger :

Trigger with phpmyadmin

but it throws the error :

MySQL meldet: #1422 - Explicit or implicit commit is not allowed in stored function or trigger.

Then I tried to create it manually with begin and end statement, but it throws another error:

manually trigger

If I do the same trigger with a simple statement like delete from users the trigger works. Does someone know how to create the trigger with a create statement?

And optionally how to create the table name like user_22 ( with the userid from users insert)?

Upvotes: 0

Views: 994

Answers (1)

Shadow
Shadow

Reputation: 34231

All data definition language (DDL) statements, such as create, alter, drop, cause implicit commit of transactions, therefore they are not allowed in user defined function or triggers because these are supposed to run in the same transaction as the sql statement that invoked them. You can create a stored procedure that inserts the data in the user table and creates another table, but I would not do that either.

Moreover, you seem to have a serious design flaw: you should not create a separate table for each user. You can store user related information in a single table, where the user id will tell you which user a certain record belongs to.

Upvotes: 1

Related Questions