Re: INSERT Trigger to check for existing records

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