The Heroku Connect Trigger Log
Last updated November 30, 2021
Table of Contents
The trigger log captures changes made to records in Heroku Postgres. Heroku Connect uses the trigger log to send data to Salesforce. This article helps you understand how it works and how to read it.
Two tables in the schema configured for your Heroku Connect add-on house the trigger log. Connect captures new changes in the
_trigger_log table. Processed records move from the
_trigger_log table to the
Heroku Connect processes all read-write captured changes to send to Salesforce in a single process per add-on. Each trigger log entry has a state that indicates if Heroku Connect has processed it. While the trigger log also captures changes for read-only mappings, Heroku Connect takes no actions on these changes.
Changing a mapping from read-write to read-only doesn’t prevent the sending of already captured changes to Salesforce.
When an INSERT occurs on a mapped read-write table, all the columns are captured. After a successful INSERT into Salesforce, Connect captures the
sfid of the newly created record and writes that back to the originating table in Postgres.
When an UPDATE occurs on a mapped read-write table, the trigger compares the values in the row before and after the change. It records only the changed values.
A DELETE operation records just the delete.
If a TRUNCATE operation occurs on a mapped read-write table, the records don’t get deleted in Salesforce. Triggers on mapped tables only apply to INSERT, UPDATE, and DELETE operations. TRUNCATE operations don’t get captured in the
_trigger_log table. Delete the records in Salesforce separately yourself.
Connect isn’t able to observe change within transactional bounds and can’t enforce a strict ordering of writes as they occurred in the DB.
Access the Trigger Log
You can view the
_trigger_log table of your database by using
SELECT table_name, record_id, action, sf_message FROM salesforce._trigger_log;
Trigger Log Retention
Demo Heroku Connect add-on plans keep changes in the
_trigger_log_archive table for 7 days, while paid add-ons keep changes in the archive for 30 days. Connect automatically purges changes older than 30 days.
_trigger_log_archive tables have the same structure.
|id||integer||Table Primary Key. Also used to determine order of changes.|
|created_at||timestamp||Time change captured.|
|updated_at||timestamp||Changed when entry is modified by Heroku Connect.|
|processed_at||timestamp||Time when Heroku Connect finished working on the entry.|
|table_name||varchar(128)||Table the change was captured from.|
|sfid||varchar(18)||Salesforce ID of the captured change (if applicable).|
|action||varchar(7)||The operation captured: INSERT, UPDATE, or DELETE.|
|state||varchar(8)||The current state of the record (see States).|
|values||text||hstore encoded column values captured for the change.|
|sf_message||text||Any error messages returned from Salesforce when an error occurs during writing. (See Heroku Connect Write Errors).|
|old||text||The old value captured from before the change encoded in hstore format.|
The trigger log tracks the state of each entry in the
state field. The end state of each entry can be: SUCCESS, MERGED, IGNORED, FAILED or READONLY. Additionally, the entry can go through the states NEW, PENDING, IGNORE and BULKSENT
|SUCCESS||The row was successfully written to Salesforce.|
|MERGED||This only applies to the Merged Writes algorithm. Several changes were made to a single row within a single polling interval. Connect merges all of the changes into a single API request to Salesforce.|
|IGNORED||Connect didn’t attempt to write the record to Salesforce. This state can happen if you update a record, but don’t actually change any data that syncs back to Salesforce (unmapped fields).|
|FAILED||Connect tried to write the record, but failed. The
|READONLY||A captured change from a read-only table. Connect takes no action on these changes.|
|NEW||A newly captured change ready for processing.|
|IGNORE||A newly captured change that syncs back to Salesforce (unmapped fields).|
|PENDING||Connect is processing this change.|
|BULKSENT||Bulk write is in progress, but not completed yet.|
Troubleshooting With the Trigger Log
The trigger log is useful for troubleshooting write errors. See Heroku Connect Write Errors for more info.