Hi Everybody,

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 and _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 'base64'.

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.

Thanks

S with ♥