What are the advantages/disadvantages of using MySQL triggers to keep data synced between one table and another and even update aggregate/report tables as data changes?
By aggregate/report tables I mean tables that summarise data that exists in other tables such as:
tbl_user_location:
1 Mike New York
2 John London
3 Emily Paris
4 Jack New York
tbl_summary:
New York 2
London 1
Paris 1
As data changes real-time in tbl_user_location the summary is updated.
What are alternatives to this approach?
In advantage: SQL triggers provide an alternative way to run scheduled tasks. triggers are invoked automatically before or after a change is made to the data in tables.
In disadvantage: However, SQL triggers are invoked and executed invisibly from client-applications therefore it is difficult to figure out what happen in the database layer. This may increase the overhead of the database server.
If you are talking about high velocity data don't even think about triggers. I suggest you to go with stream processing technique. You can you mysql bin-logs to capture events and add to the stream and then process. Simply triggers is not a good idea for real time data aggregation.
Try something like below, you might need to do some searching on these Ex: (mysql binlogs) maxwell->kafka->spark (processed data)