CODEWITHSUNDEEP
sql-serversql-server-2005dynamictriggers
Steve
Steve

Reputation: 4623

Create SQL trigger query to dump all column changes into single variable

For some background... I have a collection of tables, and I would like a trigger on each table for INSERT, UPDATE, DELETE. SQL Server version is SQL 2005.

I have an audit table Audit that contains a column called Detail. My end goal is to create a trigger that will get the list of columns of its table, generate a dynamic select query from either Inserted, Updated, or Deleted, do some string concatenation, and dump that value into the Detail column of Audit.

This is the process I was thinking:

  1. Get columns names in table for sys.columns
  2. Generate dynamic sql SELECT query based on column names
  3. Select from Inserted
  4. foreach row in results, concatenate column values into single variable
  5. Insert variable data into Detail column

So, the questions:

Is this the best way to accomplish what I'm looking to do? And the somewhat more important question, how do I write this query?

Upvotes: 0

Views: 1319

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

You could use FOR XML for this, and just store the results as an XML document.

SELECT *
FROM Inserted
FOR XML RAW

will give you attibute-centric xml, and

SELECT *
FROM Inserted
FOR XML PATH('row')

will give you element-centric xml. Much easier than trying to identify the columns and concatenate them.

Upvotes: 3

Related Questions