Re: INSERT Trigger to check for existing records

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Saturday, November 21, 2020, Hagen Finley <hagen@xxxxxxxxxxxxxx> wrote:

I pull a new forecast spreadsheet each Monday. 80% of the records are the same as the existing records from the week before.

Here’s what I (REALLY) want:

Trigger looks at three fields prior to new insert: Deal ID (numeric), revenue (numeric), stage(char)     Example: 19743576    22072.37    Commit - 90%

1. If the NEW dealid doesn't match any of the OLD dealids, insert the new row
2. if the NEW dealid, revenue and stage fields ALL match the OLD dealid, revenue and stage, skip (don't insert the NEW row)
3. If the NEW dealid matches an OLD dealid but either the NEW revenue OR the stage fields have changed (don't match OLD record) insert new row (I'll review both rows manually)
   

Am I anywhere close (same county) to the right code?

IMO, don’t use triggers.  Load the data into a temporary, or unlogged table, and then run commands to do what you want against the live tables.  Truncate/drop before doing that again the following week.

David J.