We have a Customer table that already exists in Heroku and we want to map that table to a custom object in Salesforce. When the Postgres table is updated, we want the corresponding custom object record to be updated as well.
More importantly, when this custom object record is updated, we want to be able to fire a trigger or a workflow rule.
The existing Table has tens of thousands of rows currently. We’d use the External Objects approach but I don’t believe we can fire triggers on that object. We’d use Connect but I believe that would just add another table to our Heroku instance and wouldn’t sync with our existing Customer table.
What would be the best approach to take? Could we write a batch class to run every 15 minutes to query external object records and perform our intended logic that will update related standard object records? Would it be better to write logic in Heroku that joins our custom object table with our Customers table?
Any insight would be appreciated!