CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Greg Gum
Greg Gum

Reputation: 37875

How to determine what fields were update in an update trigger

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

Upvotes: 4

Views: 3547

Answers (2)

roman
roman

Reputation: 117337

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

Upvotes: 6

Lajos Veres
Lajos Veres

Reputation: 13725

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)"

Upvotes: 0

Related Questions