LogTrigger

[gamm...@gmail.com]

Dear Robbie,

I would like to implement the log trigger (or history trigger) pattern (https://en.wikipedia.org/wiki/Log_trigger) for certain subset of objects within the database. 

Let me give you an example of an actual use case: let's say we have three classes (collections), Category, Article and Order, connected like this:

Category   1----*   Article   1----*   Order

Now, Category and Article have corresponding history collections CategoryHistory and ArticleHistory, which record insert, update and delete operations 

on the records within the Category and the Article collections, respectively, adding information of the time interval in which a certain object was valid.

A simplified setup looks like this:

Category:

- ID

- name

CategoryHistory:

- ID

- categoryID

- categoryName

- startDate

- endDate

Article:

- ID

- name

- price

ArticleHistory:

- ID

- articleID

- articleName

- articlePrice

- startDate

- endDate

An Order object looks like:

Order:

- articleID

- orderDate

Category as well as Article objects may change over time, but an Order object needs to know the exact state the Article object and (!) the Category object were in at time orderDate.

There are two questions arising:

1. How to best implement the trigger (e.g. how/where to monitor object changes and insert the objects into the history table)?

2. I thought about using a view that represents a certain snapshot of the data at time orderDate. Do we need one such view per Order object (that would be a lot of views)? 

If so, is this an efficient approach? 

Thank you!