Re: INSERT Trigger to check for existing records

On Saturday, November 21, 2020, Hagen Finley <hagen(at)datasundae(dot)com> 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.