On 11/21/20 9:47 AM, Hagen Finley wrote:
Thanks so much Adrian,
I like this approach but as you indicated it doesn't actually NULL the
INSERT.
It should cause the INSERT not to happen if a row exists with the same
values for ndealid, revusd and stage. Are you seeing an INSERT for those
conditions?
Could we UPDATE the existing record (per my fledgling chk UPDATE and
then RETURN NULL? (More proof I don't know what I am talking about ;-).
The INSERT won't happen so I'm not sure what you want to check against?
Hagen
On 11/21/20 10:11 AM, Adrian Klaver wrote:
On 11/21/20 8:47 AM, Adrian Klaver wrote:
On 11/21/20 8:20 AM, Adrian Klaver wrote:
On 11/21/20 8:00 AM, Hagen Finley wrote:
Hello,
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;
Well this is what happens when I answer BC(before coffee). The above
will not work, if for no other reason then OLD does not exist in an
INSERT. Will try to come up with something that is in the realm of
possibility.
Alright caffeine in the blood stream, so something that might actually
work:
DECLARE
match_ct integer;
BEGIN
SELECT INTO
match_ct count(*)
FROM
sfdc
WHERE
ndealid = NEW.ndealid
AND
revusd = NEW.revusd
AND
stage = NEW.stage;
IF match_ct > 0 THEN
RETURN NULL; --Will cancel INSERT
ELSE
RETURN NEW;
END IF;
END;
Though I would also point you at David's solution. Given that you are
only looking at ~20% of the records being different it would save you
a lot of churning through INSERTs.
Hagen
Larimer County, CO
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
