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