How to determine what fields were update in an update trigger

Question

UPDATE: Using Update_Columns() is not an answer to this question, as the fields may change in the order which will break the trigger (Update_Columns depends on the column order).

UPATE 2: I already know that the Deleted and Inserted tables hold the data. The question is how to determine what has changed without having to hard code the field names as the field names may change, or fields may be added.

Lets say I have a table with three fields.

The row already exists, and now the user updates fields 1 and 2.

How do I determine, in the Update Trigger, what the field were updated, and what the before and after values where?

I want to then log these to a log table. If there were two fields update, it should result in two rows in the history table.

Table
Id  intField1  charField2  dateField3
7           3  Fred        1995-03-05

Updated To

7           3  Freddy      1995-05-06

History Table
_____________

Id  IdOfRowThatWasUpdated    BeforeValue    AfterValue (as string)
1                       7    Fred           Freddy
2                       7    1995-03-05     1995-05-06

I know I can use the Deleted table to Get the old values, and the inserted table to get the new values. The question however, is how to do this dynamically. In other words, the actual table has 50 columns, and I don't want to hard code 50 fields into a SQL statement, and also if the fields change, and don't want to have to worry about keeping the SQL in sync with table changes.

Greg

possible duplicate of [sql to detect fields modified in update trigger ( sql server 2005 )?](http://stackoverflow.com/questions/434842/sql-to-detect-fields-modified-in-update-trigger-sql-server-2005) — Hamlet Hakobyan, Sep 29 '13 at 11:10
@HamletHakobyan The question is different - my question is how to 'dynamically' determine which fields changed. — Greg Gum, Sep 29 '13 at 11:42
`as the fields may change in the order` Do you mean that you will change your scheme? The you can change your trigger. Also, it is easy to transfer ordinal position to name. — Hamlet Hakobyan, Sep 29 '13 at 11:57

score 6 · Accepted Answer · edited May 23 '17 at 11:47

6

you can use one of my favorite XML-tricks to do this:

create trigger utr_Table1_update on Table1
after update, insert, delete
as
begin
    with cte_inserted as (
        select id, (select t.* for xml raw('row'), type) as data
        from inserted as t
    ), cte_deleted as (
        select id, (select t.* for xml raw('row'), type) as data
        from deleted as t
    ), cte_i as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_inserted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    ), cte_d as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_deleted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    )
    insert into Table1_History (ID, Name, OldValue, NewValue)
    select
        isnull(i.ID, d.ID) as ID,
        isnull(i.Name, d.Name) as Name,
        d.Value,
        i.Value
    from cte_i as i
        full outer join cte_d as d on d.ID = i.ID and d.Name = i.Name
    where
        not exists (select i.value intersect select d.value)

end;

sql fiddle demo

edited May 23 '17 at 11:47

Community

1
1

answered Sep 29 '13 at 11:39

Roman Pekar

107,110
28
195
197

1

If new value is `NULL` and old value is an empty string or `' '` then the last predicate should be replaced with `where i.Value <> d.Value or i.Value IS NULL AND d.Value IS NOT NULL or i.Value IS NOT NULL AND d.Value IS NULL` or `where EXISTS(SELECT i.Value EXCEPT SELECT d.Value)` . [SQLFiddle](http://www.sqlfiddle.com/#!3/d41d8/21670) – Bogdan Sahlean Sep 29 '13 at 12:32
1

@BogdanSahlean Thanks for this, will include it. – Greg Gum Sep 29 '13 at 12:41
1

@BogdanSahlean yep, forgot to mention that, nice use of except BTW – Roman Pekar Sep 29 '13 at 13:38
Is there a way to exclude a Field? For example, there is an 'UpdatedOn' on field that I don't need. – Greg Gum Sep 29 '13 at 19:36
1

@GregHollywood just add and `isnull(i.Name, d.Name) not in ('UpdatedOn)` to the final where – Roman Pekar Sep 29 '13 at 19:39
@BogdanSahlean actually I think I can use `not exists (select i.value intersect select d.value)` for comparing values, thanks for pointing me in that direction – Roman Pekar Sep 30 '13 at 09:14
I know this post is a few years old but WOW that is brilliant and worked for me. Thanks! I modified mine to include the table the change was coming from and the System User so I know who changed it and also excluded the Time Stamp column I have in all my tables. I created a History table with columns TableName, ID, Name, OldValue, NewValue, ByUser. This way I can add this trigger to any table. – Dave Stuart Feb 27 '19 at 03:41

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

In this post:

How to refer to "New", "Old" row for Triggers in SQL server?

It is mentioned that/how you can access the original and the new values, and if you can access, you can compare them.

"INSERTED is the new row on INSERT/UPDATE. DELETED is the deleted row on DELETE and the updated row on UPDATE (i.e. the old values before the row was updated)"

edited May 23 '17 at 10:31

Community

1
1

answered Sep 29 '13 at 11:12

Lajos Veres

13,595
7
43
56

How to determine what fields were update in an update trigger

2 Answers2