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@aklaver.com
