MySQL: How to make Trigger to insert row in another table?

Question

Please read everything, included the notes and edits at the end

Problem

I have a MySQL DB with 3 different table:

1. To save users

mysql> describe users;
+-------------+-----------------------------------+------+-----+---------+----------------+
| Field       | Type                              | Null | Key | Default | Extra          |
+-------------+-----------------------------------+------+-----+---------+----------------+
| id          | int                               | NO   | PRI | NULL    | auto_increment |
| firstname   | varchar(255)                      | NO   |     | NULL    |                |
| lastname    | varchar(255)                      | YES  |     | NULL    |                |
| username    | varchar(255)                      | NO   | UNI | NULL    |                |
| pswd        | varchar(255)                      | NO   |     | NULL    |                |
| permissions | enum('admin', 'student')          | NO   |     | NULL    |                |
| active      | tinyint(1)                        | NO   |     | 1       |                |
+-------------+-----------------------------------+------+-----+---------+----------------+
7 rows in set (0,01 sec)

2. To save students

mysql> describe students;
+--------------+--------------+------+-----+--------------+-------------------+
| Field        | Type         | Null | Key | Default      | Extra             |
+--------------+--------------+------+-----+--------------+-------------------+
| id           | int          | NO   | PRI | NULL         |                   |
| firstname    | varchar(255) | NO   |     | not assigned |                   |
| lastname     | varchar(255) | YES  |     | NULL         |                   |
| id_number    | int          | NO   | UNI | -1           |                   |
| registration | date         | NO   |     | curdate()    | DEFAULT_GENERATED |
+--------------+--------------+------+-----+--------------+-------------------+
5 rows in set (0,01 sec)

3. To save administrators

mysql> describe admins;
+--------------+--------------+------+-----+--------------+-------------------+
| Field        | Type         | Null | Key | Default      | Extra             |
+--------------+--------------+------+-----+--------------+-------------------+
| id           | int          | NO   | PRI | NULL         |                   |
| firstname    | varchar(255) | NO   |     | not assigned |                   |
| lastname     | varchar(255) | YES  |     | NULL         |                   |
| registration | date         | NO   |     | curdate()    | DEFAULT_GENERATED |
+--------------+--------------+------+-----+--------------+-------------------+
4 rows in set (0,00 sec)

I want to create an after insert trigger for users to insert a row in one of the others, based in the field users.permissions.

• Trigger:

delimiter $$
create trigger users_ai
after insert
on users for each row
begin
    set @id_to_update := new.id;
    set @table_to_update := (select permissions
                            from users
                            where id=@id_to_update
                        );  
    set @query := concat(
        'update ',
        quote(@table_to_update),
        's set firstname=(select firstname from users where id=',
        quote(@id_to_update),
        '),lastname=(select lastname from users where id=',
        quote(@id_to_update),
        ') where id=',
        quote(@id_to_update)
    );
    exec @query;
end;
$$
delimiter ;

But, always get the following error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Error position: line: 4

DBeaver (the editor I use to work with DB) throw the following details:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:920)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3805)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5068)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
    at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
    ... 12 more

What I tried

1. Check the basic things as syntax, spaces or weird characters.
2. Different forms to set @id_to_update variable in trigger:
   • new.id
   • last_insert_id()
   • This code on StackOverflow:

     set @id_to_update = (select id from users order by id desc limit 1);
     if @id_to_update is null or @id_to_update = '' then set @id_to_update = 1;
     

Notes

1. My knowledge of DB are basics.
2. This is just a part of a "Toy Project" I'm making in my free time, just to learn new things.
3. MySQL version:

   ~ mysql --version
   mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
   

Edit

Based on the answer of @Ergest Basha and other recommendations, replaced everything to conditions, make some changes on the tables and try to keep everything more simple, now looks like:

1. Users:

mysql> describe users;
+-------------+-----------------------------------+------+-----+---------+----------------+
| Field       | Type                              | Null | Key | Default | Extra          |
+-------------+-----------------------------------+------+-----+---------+----------------+
| id          | int                               | NO   | PRI | NULL    | auto_increment |
| firstname   | varchar(255)                      | NO   |     | NULL    |                |
| lastname    | varchar(255)                      | YES  |     | NULL    |                |
| username    | varchar(255)                      | NO   | UNI | NULL    |                |
| pswd        | varchar(255)                      | NO   |     | NULL    |                |
| permissions | enum('admin','student')           | NO   |     | NULL    |                |
| active      | tinyint(1)                        | NO   |     | 1       |                |
+-------------+-----------------------------------+------+-----+---------+----------------+
7 rows in set (0,01 sec)

2. Students:

mysql> describe students;
+--------------+--------------+------+-----+-----------+-------------------+
| Field        | Type         | Null | Key | Default   | Extra             |
+--------------+--------------+------+-----+-----------+-------------------+
| id           | int          | NO   | PRI | NULL      |                   |
| firstname    | varchar(255) | NO   |     | NULL      |                   |
| lastname     | varchar(255) | YES  |     | NULL      |                   |
| id_number    | int          | NO   | UNI | -1        |                   |
| registration | date         | NO   |     | curdate() | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-----------+-------------------+
5 rows in set (0,00 sec)

3. Admins:

mysql> describe admins;
+--------------+--------------+------+-----+-----------+-------------------+
| Field        | Type         | Null | Key | Default   | Extra             |
+--------------+--------------+------+-----+-----------+-------------------+
| id           | int          | NO   | PRI | NULL      |                   |
| firstname    | varchar(255) | NO   |     | NULL      |                   |
| lastname     | varchar(255) | YES  |     | NULL      |                   |
| registration | date         | NO   |     | curdate() | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-----------+-------------------+
4 rows in set (0,00 sec)

4. Trigger for users table:

delimiter $$
create trigger users_ai
after insert
on users for each row
begin
    case new.permissions
        when 'student' then
            insert into students (id, fistname, lastname)
            values (new.id, new.firstname, new.lastname);
        else
            insert into admins (id, firstname, lastname)
            values (new.id, new.firstname, new.lastname);
    end case;
end
$$
delimiter ;

But still not works and keep getting the same error after the begin statement:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8

At this point don't know if is just an error on my machine, all advice is heartily appreciated.

Answer 1

I want to create an after insert trigger for users to insert a row in one of the others, based in the field users.permissions.

This should be a simple if else condition.

The trigger verifies the permissions inserted value, if equal to admin then insert into admins table otherwise insert into the students table, as your datatype was enum and I expect the other value to be student .

create trigger insert_admin_or_srudent after insert on users for each row
begin
  if new.permissions='admin'
    then insert into admins (firstname,lastname) values (NEW.firstname,NEW.lastname);
     else insert into students (firstname,lastname,username) values (NEW.firstname,NEW.lastname,NEW.username);
End if;
end

Data example

create table users (
    id int  ,                     
    firstname varchar(255),        
    lastname varchar(255),
    username varchar(255),
    pswd varchar(255),
    permissions enum('admin', 'student'),
    active tinyint(1)
 );
 
 create table students (
    id int  ,                     
    firstname varchar(255),        
    lastname varchar(255),
    username varchar(255),
    id_number int,
    registration date 
 );
 
  create table admins (
    id int  ,                     
    firstname varchar(255),        
    lastname varchar(255),
    registration date 
 );

insert into users values 
(1,'test1','test1','tst1','tst1','admin',1),
(1,'test2','test2','tst2','tst2','student',1);

Working example

Answer 2

near '' means "at the end". This usually means unbalanced parens, etc. However, I don't see such. Instead, I see 2 other things that may be wrong:

• exec --> execute
• The query must be in a DECLAREd variable, not an @varable.

Answer 3

Based on @Andriy M on the comments of my question, the problem seems to be with my environment, not the code itself.

For anyone curious, here is an online demo he made to show it.