Last updated November 29, 2022

Heroku Connect uses Postgres triggers to observe changes in your Connect tables and write your changes to Salesforce. Implementing your own custom triggers on Connect tables can create additional load and Connect can miss your updates.

Understanding xmlbinary

Heroku Connect uses a Postgres variable called xmlbinary in its own triggers. The value of xmlbinary determines whether to send updates to Salesforce. This variable is how Connect prevents sending its own updates back to Salesforce and creating an infinite loop.

Connect triggers detect row updates to send to Salesforce when the xmlbinary value is base64. Connect triggers ignore row updates when xmlbinary is hex. You can set this value in your own custom trigger to temporarily enable or disable sending updates to Salesforce.

Make Changes in Response to Data Coming from Salesforce or To Foreign Tables

When reacting to changes coming from Salesforce, use an AFTER trigger to temporarily change the value of xmlbinary to base64. Restore the previous value after you have made your changes. This method also works well if you’re altering data in another table that requires capturing.

Here’s an example for a Contact mapping:

1. A Contact record created in Salesforce and written to your Heroku Postgres database with Connect as part of normal sync operations.
2. A trigger updates the row in Heroku Postgres with an externalid__c.
3. Heroku Connect detects this update and syncs it back to Salesforce.

The custom trigger for the Contact example looks like:

CREATE OR REPLACE FUNCTION salesforce.contact_external_id_proc() 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 LOCAL xmlbinary TO 'base64';

  -- Add your custom trigger code here.

  -- Update the external ID
  UPDATE salesforce.contact SET externalid__c = gen_random_uuid()
  WHERE id = NEW.id;

  -- Your custom trigger code ends here.

  -- Reset the value
  EXECUTE 'SET LOCAL xmlbinary TO ' || oldxmlbinary;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS contact_after_trigger ON salesforce.contact;

CREATE TRIGGER contact_after_trigger
  AFTER INSERT OR UPDATE ON salesforce.contact
  FOR EACH ROW
  WHEN (get_xmlbinary()::text = 'hex'::text AND NEW.externalid__c IS NULL)
  EXECUTE PROCEDURE salesforce.contact_external_id_proc();

Connect often INSERTs, UPDATEs, or DELETEs thousands of records at a time. Ensure that custom triggers perform well with large volume of records by testing in staging. These triggers also work best when they don’t write many changes per record change. Additionally, use simple calculations over complex calculations. Complex calculations can have an adverse impact on the speed of writing data from Salesforce.

Alter the Result of Your Own Data Change Statements

You can also use custom triggers to alter the data captured by Connect in response to your own SQL statements. For example, you can ensure that a UUID External ID is inserted every time you write a new record to your database. In this case, use a BEFORE trigger so that Connect picks up with the original INSERT statement.

Continuing with the Contact example from the previous section, the BEFORE trigger looks like this:

CREATE OR REPLACE FUNCTION salesforce.contact_external_id__before_proc() RETURNS TRIGGER AS $$

BEGIN
  -- Add your custom trigger code here.

  -- Update the external ID
  NEW.external_id__c := gen_random_uuid();

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS contact_before_trigger ON salesforce.contact;

CREATE TRIGGER contact_before_trigger
  BEFORE INSERT OR UPDATE ON salesforce.contact
  FOR EACH ROW
  WHEN (get_xmlbinary()::text = 'base64'::text AND NEW.externalid__c IS NULL)
  EXECUTE PROCEDURE salesforce.contact_external_id_before_proc();

In this case, the WHEN condition guarantees that xmlbinary is base64. There’s no need to set or unset its value to ensure that Connect captures the change with the rest of the record.