Finding changed column values

Question

Table 1

 ID |COL2|COL3|COL4|COL5|
ROW1|  1 |  1 | 1  | 1  |
ROW2|  1 |  3 | 2  | 6  |
ROW3|  1 |  1 | 1  | 1  |
ROW4|  1 |  1 | 1  | 1  |
ROW5|  1 |  1 | 1  | 1  |
ROW6|  1 |  1 | 1  | 1  |

Problem

After updating table 1, I have a new row with changed values:

 ID |COL2|COL3|COL4|COL5|
ROW1|  1 |  1 | 1  | 1  |
ROW2|  1 |  0 | 4  | 5  |
ROW3|  1 |  1 | 1  | 1  |
ROW4|  1 |  1 | 1  | 1  |
ROW5|  1 |  1 | 1  | 1  |
ROW6|  1 |  1 | 1  | 1  |

I want to select the row from table 1 that was changed into multiple rows based on the column name.

Desired output

ID  |OLD|NEW|
ROW2| 3 | 0 |
ROW2| 2 | 4 |
ROW2| 6 | 5 |

My attempt

I have created a trigger like:

select d.col2 as old, i.col2 as new 
from inserted i 
inner join deleted d 
    on i.id = d. id 
where not exists
(
    select i.row2 
    intersect 
    select d.row2
) 

...but it just returns one row.

How I can I achieve the output I want? I am using SQL Server 2008.

Answer 1

Sample table and data

CREATE TABLE dbo.Table1
(
    RowID integer IDENTITY PRIMARY KEY,
    Col2 integer NULL,
    Col3 integer NULL,
    Col4 integer NULL,
    Col5 integer NULL
);

INSERT dbo.Table1
    (Col2, Col3, Col4, Col5)
VALUES
    (1, 1, 1, 1),
    (1, 3, 2, 6),
    (1, 1, 1, 1),
    (1, 1, 1, 1),
    (1, 1, 1, 1),
    (1, 1, 1, 1);

Trigger

CREATE TRIGGER Table1_AU
ON dbo.Table1
AFTER UPDATE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    SELECT
        I.RowID,
        Changed.Name,
        Changed.Old,
        Changed.New
    FROM Inserted AS I
    JOIN Deleted AS D
        ON D.RowID = I.RowID
    CROSS APPLY
    (
        -- Unpivot only changed columns
        SELECT 'Col2', D.Col2, I.Col2
        WHERE NOT EXISTS (SELECT D.Col2 INTERSECT SELECT I.Col2)
        UNION ALL
        SELECT 'Col3', D.Col3, I.Col3
        WHERE NOT EXISTS (SELECT D.Col3 INTERSECT SELECT I.Col3)
        UNION ALL
        SELECT 'Col4', D.Col4, I.Col4
        WHERE NOT EXISTS (SELECT D.Col4 INTERSECT SELECT I.Col4)
        UNION ALL
        SELECT 'Col5', D.Col5, I.Col5
        WHERE NOT EXISTS (SELECT D.Col5 INTERSECT SELECT I.Col5)
    ) AS Changed (Name, Old, New)
    WHERE NOT EXISTS
    (
        -- Only changed rows
        SELECT I.Col2, I.Col3, I.Col4, I.Col5
        INTERSECT
        SELECT D.Col2, D.Col3, D.Col4, D.Col5
    );
END;

Update statement

UPDATE dbo.Table1
SET Col3 = 0,
    Col4 = 4,
    Col5 = 5
WHERE
    RowID = 2;

Output

Note the returning results from triggers is deprecated, but presumably the question is a simplified version of the actual requirement. This code assumes the RowID column will never change (and cannot, in this example due to the identity property).