1

Background -

Table that looks like this:

country_id
product_id
flow_date
update_date
amount

country_id, product_id, flow_date, and update_date are a composite PK, because I need to track the history of updates.

However, many times, data is inserted to the table where the amount hasn't changed. (I.e. country, product, flow date and amount are the same, but update_date is new.) In those cases, I just want to update the update_date of the existing entry, rather than insert a new field. Otherwise, I want it to insert as usual. (I'm really not sure that the below will do this later part.)

This is what I have so far:

CREATE TRIGGER history_archive_changes_only
ON history_archive
AFTER INSERT
AS
BEGIN
    UPDATE history_archive 
    SET update_date = i.update_date
    from history_archive
    INNER JOIN inserted i on (i.country_id = history_archive.country_id
                            AND i.product_id = history_archive.product_id
                            AND i.flow_date = history_archive.flow_date
    WHERE abs(history_archive.amount - i.amount) < 0.01

END;

Thoughts?

0
3

Instead of:

  • Update a row instead of inserting it when you see that the amount hasn't changed.

Why not:

  • Just insert the row. If you then find previous rows where there are multiple consecutive rows with the same value, delete all but the most recent one.

Create this trigger against a test version of your table please:

CREATE TRIGGER dbo.hist_arch_test ON dbo.history_archive_test_copy
FOR INSERT AS
BEGIN
  IF (@@ROWCOUNT > 0 AND UPDATE(amount))
  BEGIN
    ;WITH x AS 
    (
      SELECT *, delta = ABS
           (
             /* amount from *next* row */
             LEAD(a.amount, 1) OVER 
             (
               PARTITION BY a.country_id, a.product_id, a.flow_date 
               ORDER BY a.update_date
             ) 

             -  /* minus */

             /* amount from *current* row */
             a.amount
           )
      FROM dbo.history_archive_test_copy AS a
      WHERE EXISTS
      (
        SELECT 1 FROM inserted AS i
          WHERE i.country_id = a.country_id 
            AND i.product_id = a.product_id 
            AND i.flow_date  = a.flow_date
      )
    )
    SELECT *, status = CASE delta WHEN 0 THEN 'mark as delete' ELSE '' END
    FROM x ORDER BY country_id, product_id, flow_date, update_date;
    -- when happy, delete the select, uncomment the delete:
    -- DELETE x WHERE delta = 0;
  END
END

I didn't test all possible permutations of existing values but this should clean up any old junk not directly related to the most recent row/series too.

2
  • Thanks. I see where the "Where Exists" clause only give me the rows that are duplicative, but i'm missing how the Lead give me the delta to the inserted value. It seems I'll get the delta to each other, but not to i.amount.
    – Marc
    Aug 24 '20 at 13:23
  • @Marc LEAD(a.amount,1) gives the value from the next row for that combination, ordered by date, then - a.amount subtracts from that the value from the current row. Basically inside x there is an expression called delta and it is <amount from next row> - <amount from this row>. Aug 24 '20 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.