Re: INSERT Trigger to check for existing records

On 11/21/20 8:00 AM, Hagen Finley wrote:
> 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'

Not sure how.
More comments below.

>
>
> |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.

What client are you using?

>
>
> *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?

Don't do the UPDATE. Also I thought when the values matched you did
not want INSERT?

Instead:

IF NEW.ndealid = OLD.ndealid AND NEW.revusd = OLD.revusd
AND NEW.stage = OLD.stage THEN
RETURN NULL; --Will cancel INSERT
ELSE
RETURN NEW;

END IF;

>
> Hagen
>
> Larimer County, CO
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com