0

I have no experience in writing MySQL triggers, my boss is asking me to write a trigger which does the following.

There are 5 tables (table1, table2, table3, table4, and table5) in the database that we are trying to backup with triggers, but it is not just about the backup, I will explain everything below:

  1. Create backup table: create table table1_backup as select * from table1 (for all tables). - This is a one-time activity.
  2. Compare Data of Both Tables.
  3. Delete everything from table1 (data, partition) and keep only 3 days data (Present, Yesterday, and Day Before) - Every Day

I would also like to add these tables have partition.

partition_date | count(partition_date) | +----------------+-----------------------+ | 2023-02-19 | 4837 | | 2023-02-18 | 20213 |

The catch is my boss wants to compare and verify data, if any action fails the daily process should also rollback.

I tried writing cron jobs, it works sometimes and fails sometimes, I can only verify with count function.

asim13june
  • 1
  • This sounds like a recipe for disaster. Firstly, this is not what triggers are for. Leave the data alone, but establish a read-only replica. Your integrity will be guaranteed by MySQL. You have an almost real-time backup, and a database you can run reports on without affecting the performance of the live system. – Tangentially Perpendicular Feb 19 '23 at 07:41
  • Please provide enough code so others can better understand or reproduce the problem. – Community Feb 19 '23 at 14:46

0 Answers0