Re: INSERT Trigger to check for existing records

Thanks so much Adrian,

I like this approach but as you indicated it doesn't actually NULL the
INSERT.

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 ;-).

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