trigger - MySQL defines a trigger to return an auto-incrementing ID

Question

Define a trigger in MySQL to return the self-increasing id value.

code show as below:

create trigger getAutoNewId after insert on city
for each row select new.Id;

Error when creating:

Error Code: 1415. Not allowed to return a result set from a trigger

If the above returns a data set, how should I write if I only want to return a single row of IDs?

Answer 1

You will not see the results even if you execute the query statement in the trigger. It is recommended to create a log table and insert it.

create trigger getAutoNewId after insert on city
for each row 
insert into log_table(newId, time) value(new.Id, now());