4

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.

Improve this question
0

1 Answer 1

Reset to default
3

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

Sample data

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

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

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.