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@xxxxxxxxxxx
