Re: INSERT Trigger to check for existing records - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: INSERT Trigger to check for existing records |
Date | |
Msg-id | fea79107-d23b-2725-be21-ee487031843d@aklaver.com Whole thread Raw |
In response to | INSERT Trigger to check for existing records (Hagen Finley <hagen@datasundae.com>) |
Responses |
Re: INSERT Trigger to check for existing records
|
List | pgsql-general |
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
pgsql-general by date: