INSERT Trigger to check for existing records

[Hagen Finley <hagen@xxxxxxxxxxxxxx>]

Hello,

I am definitely out over my skis here so I’ll apologize in advance 😉. Running version 12.5-1.pgdg20.04+1 on ubuntu. It’s essentially a personal database I use to ingest sales forecast spreadsheets from which I  create custom reports for my job function.

I pull a new forecast spreadsheet each Monday. 80% of the records are the same as the existing records from the week before.

Here’s what I (REALLY) want:

Trigger looks at three fields prior to new insert: Deal ID (numeric), revenue (numeric), stage(char)     Example: 19743576    22072.37    Commit - 90%

1. If the NEW dealid doesn't match any of the OLD dealids, insert the new row
2. if the NEW dealid, revenue and stage fields ALL match the OLD dealid, revenue and stage, skip (don't insert the NEW row)
3. If the NEW dealid matches an OLD dealid but either the NEW revenue OR the stage fields have changed (don't match OLD record) insert new row (I'll review both rows manually)
   

Attempt 1: Update chk field with 'same' if OLD revusd OR stage are different than the NEW revusd OR stage

CREATE TRIGGER chk4chg
BEFORE
    INSERT ON sfdc
    FOR EACH ROW
BEGIN
    UPDATE sfdc
    SET chk = 'same'
    WHERE ndealid = :NEW.ndealid
      AND revusd = :NEW.revusd
      AND stage = :NEW.stage
END chk4chg;

Remarkably, that works in that it will UPDATE the chk field with 'same'

|ndealid |revusd |stage                 |chk       |

|17713063|1300000|Propose - 60%         |same      |

However, I must manually enter the parameters in dialogue box that (inexplicably) pops up when I run this command.

Attempt 2:

CREATE OR REPLACE FUNCTION update_insert() RETURNS trigger AS $$
BEGIN
    UPDATE sfdc
    SET chk = 'same'
    WHERE ndealid = OLD.ndealid;
    AND NEW.revusd = OLD.revusd
    AND NEW.stage = OLD.stage;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION Query returned successfully in 136 msec.

That's good news but the trigger doesn't actually update. It lacks BEFORE INSERT ON sfdc FOR EACH ROW so low prospect for success :-).

Attempt 3: A little more sophisticated executing Function from Trigger

CREATE TRIGGER smart_update_same BEFORE INSERT ON sfdc
FOR EACH ROW EXECUTE FUNCTION update_insert();

CREATE OR REPLACE FUNCTION update_insert() RETURNS trigger AS $$
BEGIN
    UPDATE sfdc
    SET sfdc.chk = 'same'
    WHERE NEW.ndealid = OLD.ndealid
      AND NEW.revusd = OLD.revusd
      AND NEW.stage = OLD.stage;
END;
$$ LANGUAGE plpgsql;

These 2 CREATEs return successfully but do not update the chk field on a successful INSERT:

sales=# select ndealid,revusd,stage,chk from sfdc where ndealid = 19743576;

  ndealid   |  revusd  |         stage          | chk

 19743576 | 22072.37 | Commit - 90%           |
   19743576 | 22072.37 | Commit - 90%           |
   19743576 | 22072.37 | Commit - 90%           |

These 3 attempts won't give me what I REALLY want but I figure I could use the chk field to delete the new inserts I didn't need.

Am I anywhere close (same county) to the right code?

Hagen

Larimer County, CO