CODEWITHSUNDEEP
mysqlsqltriggers
David542
David542

Reputation: 110153

How to store record changes in a mysql table

I have two tables that stores pricing for a product --

`product`
- id
- name
- added_date
- is_removed (BOOL)

`price`
- id
- product_id
- price
- price_type
- added_date
- is_removed (BOOL)

These tables always reflect the current status of the product as it is live on the store.

However, for auditing purposes I need to keep track of the following five events:

What would be the best way to keep track of this? It would be very difficult to do this at the application level, since there are about 30 ways that these two tables can change. I was thinking of using someone like a trigger to add a log to another table, though I'm afraid that would be a big hit to these tables that are quite active (~10M writes per day).

What would be the best solution for this? Note that the tables are InnoDB.

Upvotes: 2

Views: 306

Answers (1)

Utsav
Utsav

Reputation: 8093

You can create 2 tables. One to hold delta(changed) records and another to hold previous day's snapshot from Core tables. In given example, I am only using Product table but you can extend the example for Price too.

  1. (One time process). Create another table say S and synct data from Product (P) in it.

  2. From next day, compare P and S and find the changed/new records. You can do it by left outer join.

    select p.id,p.name,p.added_date,p.is_removed,
case when S.id is null then 'Insert' 
else when S.name <> P.name or S.added_date <> P.added_date or S.is_removed <> P.is_removed
then 'Update'
else 'No Change' end as RecordStatus
from Product P
left outer join S
on P.id=S.id

  3. Now you can use this result to enter records in another table say Audit. You can use a surrogate key and date column to keep track of these records in Audit on daily basis

  4. Sync S with P at the end. (from next day repeat from step 2)

Upvotes: 1

Related Questions