Using Custom Triggers with Heroku Connect
Last updated November 29, 2022
Table of Contents
Custom triggers aren’t officially supported and Heroku Support can’t provide help with them. Proceed at your own risk.
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.
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
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
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
Contactrecord created in Salesforce and written to your Heroku Postgres database with Connect as part of normal sync operations.
- A trigger updates the row in Heroku Postgres with an
- 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
base64. There’s no need to set or unset its value to ensure that Connect captures the change with the rest of the record.