In a table in MySql database, I have a boolean column with ‘Yes’ and ‘No’ for appointment confirmation. As soon as appointment gets ‘Yes’, a notification is sent via an AWS Lambda call. I want to send a 12 hour prior notification before the day of the appointment. What is the best way to do that in My Sql utilizing triggers or stored procedure or any other way?
Advertisement
Answer
How precise do you need the notifications to be?
If you simple want for example to have the notification at 13:00 before the day, you can use a simple cron to run every day at 12:50 (depending on your setup).
If you want the notification to be 12 hours before the day depending on the booked time, for example booking is made 20:00 10.10.2020, notification is sent at 08:00 09.10.2020, you can set your cron to run every 5 minutes (again depending on your workload) and have it send notifications for that time period.
If you want precise notifications I would recommend using an external tool for the job, for example RabbitMQ with Delayed Messages plugin.
If the above is not an option you can use an “always on” script that checks for changes each second.