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