Re: INSERT Trigger to check for existing records

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