How to create a Trigger in mysql with WHERE

Question

How to create a Trigger in mysql with WHERE

Navigation

#1 by (1 votes)

0

I have a problem, it turns out that I am learning to create Trigger in mysql but I can not find the way I need it.

I have this table, which is filled with records every day with more frequency in the mornings.

I need to create a Trigger that updates the status to 2 when the current date is greater than reservation_date (datetime) and the current time is greater than reservation_time (time).

I thought that this event could be run every time there is a new record in the table. I do not know if it's right.

CREATE TRIGGER upd_stado AFTER INSERT FOR EACH ROW UPDATE SET estado = 
2 WHERE datetime() > fecha_reserva && date() > hora_reserva;

The truth is that I am learning and I would like help in the Query.

Thank you.

mysql trigger

asked by Hernan Humaña 16.08.2017 в 17:46

source

1 answer

Route error when uploading project to shared hosting. (Laravel 5.4) PHP block array

score 1 · Accepted Answer

You can not do this in a trigger. According to the documentation:

Inside a stored function or trigger, it is not allowed to modify a table that is already being used (to read or write) by the statement that invoked the function or trigger.

Which means that the table is blocked when you are inserting data in it, so you can not insert a row and update it at the same time. As of this answer, I propose the following solution:

Create a stored procedure, insert it into the target table, and then update the other row, all in one transaction. With a stored procedure, you will manually confirm the changes (insert and update).

I have not done this in MySQL, but this post seems like a good example. I hope it helps you. Greetings!