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.klaver@aklaver.com