0

I have a table of definitions data, and i'm working on tracking changes using an On UPDATE trigger. Currently I have the changes being tracked via a series of IF UPDATE([COLUMNNAME]) checks. However, this has an issue in that if columns are ever added/deleted/renamed in the future, the trigger will fail.

I had the idea to pivot the DELETED and INSERTED tables, converting them from a single row with header to two columns, one containing the row header, and one containing the row data. Then joining them on the header and finding where the data parts are different. I can then record the Header, OldData (Deleted) and NewData (Inserted) values. However, I've not been able to do this so far. I've searched a number of PIVOT and UNPIVOT examples, but none seem to be quite what I'm looking for.

As an example:

ID    Name    Value1    Value2    Value3    Value4
1    Apple    10        1/1/18    25        10.0

to:

Header    Data
ID        1
Name      Apple
Value1    10
Value2    1/1/18
Value3    25
Value4    10

Any help would be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Akkristor
  • 13
  • 2

1 Answers1

0

Sometimes you need to ask questions differently: if you look at your examples, you are actually would like to convert columns to rows. If I search on SO for 'sql convert columns to rows' the first result is this question and the answers address your issue.

Dávid Laczkó
  • 1,091
  • 2
  • 6
  • 25