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

Question 1

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

Problem

I have a MySQL DB with 3 different table:

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)

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)

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.lambda1ドル(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.lambda0ドル(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.lambda0ドル(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

Check the basic things as syntax, spaces or weird characters.

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

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

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:

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)

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)

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)

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.

Question 2

I think you are working too hard. Don't use @variables; simply use PREPARE and EXECUTE statements.

Question 3

Same thing, always get an error on the line after "begin" (line 5), with or without variables

Question 4

I get errors with exec @query;;

Question 5

My execution never reach that far, neither on DBeaver nor through CLI. The error is the same with all the changes.

Question 6

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.

Question 7

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

Question 8

I want to create more tables, but based in your example, I'm gonna use a case statement and not complicate things. Thanks

Question 9

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.

Question 10

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

Question 11

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.

Question 12

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.

Ergest Basha Ergest Basha 5,3693 gold badges8 silver badges22 bronze badges · Answer 1 · 2023-03-04 15:10:10Z

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

I want to create more tables, but based in your example, I'm gonna use a case statement and not complicate things. Thanks

Rick James Rick James 80.7k5 gold badges52 silver badges119 bronze badges · Answer 2 · 2023-03-04 20:29:32Z

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.

Share

Improve this answer

answered Mar 4, 2023 at 20:29

Rick James's user avatar

Rick James Rick James

80.7k5 gold badges52 silver badges119 bronze badges

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
– Cromega08

2023年03月05日 01:15:40 +00:00
Commented Mar 5, 2023 at 1:15

Add a comment |

Cromega08 Cromega08 315 bronze badges · Answer 3 · 2023-03-06 01:10:32Z

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.

Share

Improve this answer

answered Mar 6, 2023 at 1:10

Cromega08's user avatar

Cromega08 Cromega08

315 bronze badges

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.

Rohit Gupta
– Rohit Gupta

2023年03月06日 02:00:59 +00:00
Commented Mar 6, 2023 at 2:00

Add a comment |

Stack Exchange Network

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

Problem

What I tried

Notes

Edit

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

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

Problem

What I tried

Notes

Edit

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions