3

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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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.

6
  • I think you are working too hard. Don't use @variables; simply use PREPARE and EXECUTE statements.
    – Rick James
    Mar 4, 2023 at 2:09
  • Same thing, always get an error on the line after "begin" (line 5), with or without variables
    – Cromega08
    Mar 4, 2023 at 14:01
  • I get errors with exec @query;;
    – Rick James
    Mar 4, 2023 at 17:24
  • My execution never reach that far, neither on DBeaver nor through CLI. The error is the same with all the changes.
    – Cromega08
    Mar 4, 2023 at 18:15
  • The CASE version of your trigger seems to work for me in this online demo. Perhaps the issue has to do with running the trigger definition specifically in the environment you are using.
    – Andriy M
    Mar 5, 2023 at 19:00

3 Answers 3

2

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

1
  • I want to create more tables, but based in your example, I'm gonna use a case statement and not complicate things. Thanks
    – Cromega08
    Mar 4, 2023 at 18:06
0

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.
1
  • Same, changed both and get the same error on the same position: 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
    – Cromega08
    Mar 5, 2023 at 1:15
0

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.

1
  • At the moment your answer is a link only answer which will become invalid if the link does. You could improve your answer by adding the essential details here. Mar 6, 2023 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.