Hi!

I have a history table with this setup:
[MID
PK [IIN
[NUM_EVENTS
ACTION

NUM_EVENTS starts from 1 and adds up depending on the number of logs that MID-IIN combination has.

I have a Latest table as well that has the same layout as the History table.

What I need is to create a trigger in the History table that will:

1. Add a record in the Latest if that MID-IIN combination does not exist in the Latest.

OR

2. Update the record with latest NUM_EVENT for that specific record if it already exists.

Example:

History:
MID - 03
IIN - 00001
NUM_EVENTS - 1

Trigger should add this to the Latest table if it does not exist.

If someone adds another entry for this MID-IIN combination like:
MID - 03
IIN - 00001
NUM_EVENTS - 2

The trigger should update the previous record in Latest to reflect this newly inserted record.

Thanks in advance.