Sql-server – TSQL trigger on actual changes – update field

sql serversql-server-2012t-sql

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?

Best Answer

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.

Related Question