Re: INSERT Trigger to check for existing records

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(dot)klaver(at)aklaver(dot)com