We have a business need where we want to store an Account (business account or person account) last activity date, according to specific criteria from Event and Task the account is in (for example taking into consideration only some types of events or tasks). These criteria prevent us from using the standard field LastActivityDate.
To comply with this need we developed a method that recalculates for each account the most recent event / task matching criteria in which the account is an attendee, by querying EventRelation and TaskRelation for the account.
Today we call this method by triggers from Event and Task, every time an Event / Task is inserted, updated or deleted. However, this is a very tedious way of doing : we cannot filter the calls to the trigger as the modification of EventRelation does not update a field on the Event (same for Task).
We looked for ways to replace this trigger by using Change Data Capture on EventRelation and TaskRelation, which works for the insert of EventRelation. Unfortunately, in the case of deleting EventRelation / TaskRelation, the Change Event that we receive has only null fields and we are unable to link the notification to an Event or an Account (EventId = null and AccountId = null and RelationId = null).
Is there a way to cover all use cases (attendee insertion / attendee deletion) without calling a trigger from Event / Task every time the event is updated ?
Thank you
