displayname displayname - 2 months ago 6
MySQL Question

What is the best way to update another table based on a certain condition ON UPDATE?

I'm having two tables

subscription
and
subscription_event
. A
subscription_event
can be one of the following types:

public enum SubscriptionEventType {

CREATED,
CANCELED,
CHARGED_SUCCESSFULLY,
CHARGED_UNSUCCESSFULLY,
EXPIRED,
TRIAL_STARTED,
TRIAL_ENDED,
WENT_ACTIVE, // Subscription went active and can be charged from now on.
WENT_PAST_DUE;

public Long getValue() {
return this.ordinal() + 1L;
}
}


What I want to do is to keep the state of
subscription
to the most recent event. The problem: Those events do not come in correct order. E.g. it is possible to get a
CHARGED_SUCCESSFULLY
event before a
WENT_ACTIVE
event.

So there are several way how I can accomplish what I need. First of all I can check the condition in my application layer and always set that "most recent" state based on the timestamp of the event.

Long subscriptionId = lastRecordedEvent.getSubscriptionId();

if(event.getTimestamp() > lastRecordedEvent.getTimestamp()) {
// Since the current event is more recent than all other events
// we also have to update the subscription state
subscriptionRepository.updateState(subscriptionId, event.getTimestamp());
}


However, I do not want to do this in my application layer. Another solution would be to use a
TRIGGER
on the
subscription_event
table and let that on decide whether to update the relevant
subscription
or not. The reason why I do not go for that just yet is because I know that triggers can be easily forgotten and also be a pain to maintain. Also I know one should take every other option into account before using a
TRIGGER
but since I am not a SQL/MySQL expert I'm not aware of all my options here.

So what would be the most practicable way to keep
subscription
up-to-date in this situation?

Answer

Insert your event as usual into the table and then execute the following

UPDATE subscriptions set state=events.state
FROM subscriptions inner join events on subscriptions.id = events.subscriptionID
Where events.SubscriptionId = ? and events.Timestamp = 
   (select max(timestamp) from events where events.SubscriptionId = ?)

You will need to pass parameters for the two ?s to be the subscription id of the event you just inserted

EDIT

An alternative approach is rather than have a status field in the database, create a view for your subscriptions and always query the view instead.

CREATE VIEW vw_subscriptions as
  Select s.id, otherfields from subscription, coalesce(e.Status, 1) as status
  from subscriptions s left outer join events e on s.id=e.subscriptionId
  AND e.timestamp = 
   (select max(timestamp) from events where subscriptionId=s.id)

If you are worried about forgetting/maintaining the SQL or triggers, document them as comments in your repository functions and maintain all changes to the database as a change script that you store with your source code. That way your changes are all in your source control.

Comments