Are you struggling with your DB updates to salesforce in case of triggers? If Yes, then you are on right place. I was in same situation today and wasted my hours looking for solution.
Problem: I use Heroku Connect to sync my data between salesforce and Postgres DB. I am updating my row by my own custom trigger and noticed that updates are not going back to salesforce.
Reason: Its worth mentioning that Heroku connect maintains two tables (
_trigger_log_archive) in you DB to sync your data with salesforce. Heroku connect also created DB triggers to updated these tables to keep track of you changes and pick all of your changes from these tables at the time of syncing. _trigger_log table gets updated only if session variable
xmlbinary has value as
A value of
base64 means that row updates will be detected by the Heroku Connect triggers and sent to Salesforce. A value of
hex means that updates to database rows will be ignored by the Connect triggers.
Now whenever we update any of our record using our own custom trigger, Heroku connect trigger does not get invoked as it may invoke in parallel to our own trigger and might not get sense that there is something to sync with sfdc.
Solution: It is possible to set value of
xmlbinary in your custom trigger to temporarily enable or disable sending updates to Salesforce. By doing this, Heroku connect trigger get invoked and will send your data to salesforce.
Example: Lets say we have a custom object People and we have a field peopleid__c in there which we want to generate after a raw get inserted in database.
So solution might be writing a DB trigger which can generate random id and update the row. Something like below:
CREATE OR REPLACE FUNCTION salesforce.generate_people_id() RETURNS TRIGGER AS $$ BEGIN UPDATE salesforce.people SET peopleid__c = gen_random_uuid () WHERE id = NEW.id; RETURN NEW; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS people_after_trigger ON salesforce.people; CREATE TRIGGER people_after_trigger AFTER INSERT ON salesforce.people FOR EACH ROW EXECUTE PROCEDURE salesforce.generate_people_id();
Above example will not work as your Heroku Connect trigger might not able to detect this change. So let apply our session variable change here and do the magic.
This trigger ensures that even if Connect is writing the row to your database as part of a sync operation, the
peopleid__c update will be detected, and the value will sync back to Salesforce:
CREATE OR REPLACE FUNCTION salesforce.generate_people_id() RETURNS TRIGGER AS $$ DECLARE oldxmlbinary varchar; BEGIN -- Save old value oldxmlbinary := get_xmlbinary(); -- Change value base64 to ensure writing to _trigger_log is enabled SET SESSION xmlbinary TO 'base64'; -- This is where you start your custom trigger code. UPDATE salesforce.people SET peopleid__c = gen_random_uuid () WHERE id = NEW.id; -- This is where you end your custom trigger code. -- Reset the value EXECUTE 'SET SESSION xmlbinary TO ' || oldxmlbinary; RETURN NEW; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS people_after_trigger ON salesforce.people; CREATE TRIGGER people_after_trigger AFTER INSERT ON salesforce.people FOR EACH ROW EXECUTE PROCEDURE salesforce.generate_people_id();
Thats It folks!!
Give it a try. It made my life easier.
S with ♥