1

I made a trigger to log data whenever any CRUD operation with my tables happen. For example:

ALTER TRIGGER [Data_Created]
ON [Project_MyTable1]
AFTER INSERT
AS
    INSERT INTO [Project_Logs] (Operation, LogData)
        SELECT 'INSERT', ID
        FROM INSERTED

It works just fine, but there is plenty of columns in my table which I'd like to see in my log data column. I'm struggling to find any way to extract all row data and convert it to nvarchar or xml (? maybe) except by concatenation.

Already tried a lot of things like:

Convert(nvarchar, (Select * FROM INSERTED))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
hitpoint
  • 85
  • 8
  • 1
    This sounds like an XY problem. If you want to keep a history use a history table (either within your production table, or as a separate entity), or temporal table. Don't pivot all your data from a single insert into a "logging" table. You'll find it unmanagable. – Thom A Dec 02 '19 at 11:21
  • 1
    You should also see the 'Change Data Capture' (CDC) feature of the SQL server for the Audit log. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15 – Mukesh Arora Dec 02 '19 at 11:28
  • If the data is not `NVARCHAR` why do you want to store it in that format? You don't want your actual data to be different in any way of your logged data. In addition, you double your logged data size. – jean Dec 02 '19 at 12:13
  • 1
    Found answer to my question [here](https://stackoverflow.com/questions/11890868/how-to-store-historical-records-in-a-history-table-in-sql-server). Thanks! – hitpoint Dec 02 '19 at 12:20

1 Answers1

1

based on Microsoft documents

-- CONVERT Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

mansour lotfi
  • 524
  • 2
  • 7
  • 22