INSERT Trigger to check for existing records - Mailing list pgsql-general
From | Hagen Finley |
---|---|
Subject | INSERT Trigger to check for existing records |
Date | |
Msg-id | 3e1051a4-98d4-4a59-f382-5b82efa4c095@datasundae.com Whole thread Raw |
In response to | Re: two questions about toast (Luca Ferrari <fluca1978@gmail.com>) |
Responses |
Re: INSERT Trigger to check for existing records
Re: INSERT Trigger to check for existing records |
List | pgsql-general |
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%
- If the NEW dealid doesn't match any of the OLD dealids, insert the new row
- if the NEW dealid, revenue and stage fields ALL match the OLD dealid, revenue and stage, skip (don't insert the NEW row)
- 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
pgsql-general by date: