How to check MySQL table updates and send mail using cron job

Question

I write a request program which it contains send mail automatically whenever a new data is inserted. I want to use cron to active the request.

Whenever MySQL is updated I want to receive a mail automatically.

Answer 1

I would suggest the following solutions:

1) Add column 'Status' with default value = 'INSERTED' and create a simple program which will retrieve rows with status 'Inserted' minutely and send email notification. After the sending an email, you should update status to 'SENDED' or something like this.

ALTER TABLE `your_table` 
ADD COLUMN `STATUS` VARCHAR(45) NULL DEFAULT 'INSERTED';

Your application are scheduled by cron job should retrieve data by the query:

select <some_fields>
from  <your_table>
where status = 'INSERTED'; 

<send email logic>

update <your_table>
set status = 'SENDED'; 
where status = 'INSERTED'; 

2) Create trigger which will insert information about new rows into separate table. The next steps the same as in previous approach.

CREATE TRIGGER save_inserted_data AFTER INSERT ON your_table
FOR EACH ROW BEGIN
      insert into log_table (id)
      values (:new.id)
END;

<send email logic>

delete from log_table; 

And you should add some logic which will take in account new rows during sending email. I.e,

• 3 rows was inserted
• you retrieve 3 rows for email notification
• someone add new one
• need to mark/delete only 3 rows (without new one)