PostgreSQL Trigger that updates table where original trigger runs from

Question

I have two tables in this scenario. One is my "hot sync" table which is near-realtime bi-directional sync of data from my Salesforce Org to a Postgres table. As data changes in the source system (Salesforce), it updates that table on Postgres.

On this table in Postgres, I have a trigger that runs some logic. It basically checks to see if the record triggering it has a sent date that meets some business logic, copy that row into another schema/table to "archive" it.

This all works fine.

What I need to do however is once this row has been copied into the other table, I need to update the status of the record hot sync table. Since it is bi-directional, this will allow the data in Salesforce to reflect the changes I make from the Postgres side.

Can I place this update statement within the originating trigger or is this going to cause recursion issues?

CREATE FUNCTION salesforce.archivelogicfunc()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$   BEGIN
    IF (DATE(NEW.et4ae5__datesent__c) < NOW() - INTERVAL '180 days' 
    AND DATE(NEW.et4ae5__datesent__c) > NOW() - INTERVAL '540 days')
    THEN
      INSERT INTO archive.individualemailresult__c 
            (dateopened__c, 
             numberoftotalclicks__c, 
             datebounced__c, 
             fromname__c, 
             hardbounce__c, 
             fromaddress__c, 
             softbounce__c, 
             name, 
             lastmodifieddate, 
             opened__c, 
             ownerid, 
             subjectline__c, 
             isdeleted, 
             contact__c, 
             systemmodstamp, 
             lastmodifiedbyid, 
             datesent__c, 
             dateunsubscribed__c, 
             createddate, 
             createdbyid, 
             lead__c, 
             tracking_as_of__c, 
             numberofuniqueclicks__c, 
             senddefinition__c, 
             mergeid__c, 
             triggeredsenddefinition__c, 
             sfid, 
             id, 
             _hc_lastop, 
             _hc_err,
             isarchived)
        VALUES 
            (NEW.et4ae5__dateopened__c, 
            NEW.et4ae5__numberoftotalclicks__c, 
            NEW.et4ae5__datebounced__c, 
            NEW.et4ae5__fromname__c, 
            NEW.et4ae5__hardbounce__c, 
            NEW.et4ae5__fromaddress__c, 
            NEW.et4ae5__softbounce__c, 
            NEW.name, 
            NEW.lastmodifieddate, 
            NEW.et4ae5__opened__c, 
            NEW.ownerid, 
            NEW.et4ae5__subjectline__c, 
            NEW.isdeleted, 
            NEW.et4ae5__contact__c, 
            NEW.systemmodstamp, 
            NEW.lastmodifiedbyid, 
            NEW.et4ae5__datesent__c, 
            NEW.et4ae5__dateunsubscribed__c, 
            NEW.createddate, 
            NEW.createdbyid, 
            NEW.et4ae5__lead__c, 
            NEW.et4ae5__tracking_as_of__c, 
            NEW.et4ae5__numberofuniqueclicks__c, 
            NEW.et4ae5__senddefinition__c, 
            NEW.et4ae5__mergeid__c, 
            NEW.et4ae5__triggeredsenddefinition__c, 
            NEW.sfid, 
            NEW.id, 
            NEW._hc_lastop, 
            NEW._hc_err,
            NEW.isarchived__c)
            ON CONFLICT (id) 
            DO NOTHING;

        -- Update SF to reflect the archive
        UPDATE salesforce."et4ae5__individualemailresult__c" SET isarchived__c = true, isdeleted = true WHERE id = NEW.id; 

        END IF;
        RETURN NULL;
   END;
$BODY$;

ALTER FUNCTION salesforce.archivelogicfunc()
    OWNER TO ....;

My understanding is that the NEW.* is only going to contain the rows that caused the trigger to fire in the first place. Therefore if my trigger was fired for a single record, the update statement NEW.id should only update one record on the source table?

Trying to ensure the trigger isn't going to fire again with the update statement causing some recursive loop that I am not expecting.

My concern is:

Record is Updated
Trigger Fires and inserts record into an archive table
Update runs on the source table to update the record for the new.id
This update causes the trigger to run again. The insert would fail due to the on conflict, but the update would then run again, and again etc..

The original trigger is fired AFTER INSERT/UPDATE.

TRIGGER:

CREATE TRIGGER archivelogic_firetrigger
    AFTER INSERT OR UPDATE 
    ON salesforce.et4ae5__individualemailresult__c
    FOR EACH ROW
    EXECUTE PROCEDURE salesforce.archivelogicfunc();

UPDATE: I added a WHEN condition to my trigger. It appeared to work on a basic test, but willing to take any other advice if suggested.

CREATE TRIGGER archivelogic_firetrigger
    AFTER INSERT OR UPDATE 
    ON salesforce.et4ae5__individualemailresult__c
    FOR EACH ROW
    WHEN (pg_trigger_depth() = 0) // <-- Added to prevent recursion 
    EXECUTE PROCEDURE salesforce.archivelogicfunc();

JGH JGH · Accepted Answer · 2020-05-07T17:51:51

The easiest would be to make it a before trigger, and to replace the update by

NEW.isarchived__c = true;
NEW. isdeleted = true;
[...]
RETURN NEW;

Otherwise, you can filter the rows before running the trigger: it will be called only when isarchived__c and isdeleted have NOT changed (it may be dangerous though, just imagine someone updating ALL fields)

CREATE TRIGGER archivelogic_firetrigger
AFTER INSERT OR UPDATE 
ON salesforce.et4ae5__individualemailresult__c
FOR EACH ROW
WHEN (NEW.isarchived__c IS NOT DISTINCT FROM OLD.isarchived__c 
      AND NEW.isdeleted IS NOT DISTINCT FROM OLD.isdeleted )
EXECUTE PROCEDURE salesforce.archivelogicfunc();

PostgreSQL Trigger that updates table where original trigger runs from

1 Answers