T-SQL - Select records into concatenated text?

Question

I'm trying to investigate when & why certain rows are getting deleted in a SQL 2005 database. I've started building a trigger to log some information when a row is deleted.

My trigger is activated when row(s) are deleted from a certain table. I have it set up to log a timestamp in another logging table when the delete occurs. I'd also like to log the data that was deleted, but would prefer not to hassle with writing code for each field and value.

I know when data is deleted it can be seen (temporarily) in the "Deleted" table in SQL Server. So right after a delete, I could "SELECT * FROM Deleted" and see the data. I would like to take the contents of this table, and turn it into one large text blob that I can just save into a TEXT field in my logging table.

So... in simpler terms, is there a way I can take a recordset of one or more rows and turn it into a single string variable? all within SQL commands in my trigger? Bonus points if I can include column names.

Thanks

Answer 1

I would stay away from anything that would run too long when working in a trigger. That includes some query just to determine a static table layout (because you don't want to write the code yourself) so you can build a string.

I do this type of thing all the time, but mostly with stored procedure parameters. I have most of this in a template I use.

create this function, will display the column nicely within quotes or show NULL:

CREATE FUNCTION [dbo].[QuoteNull]
(
     @InputStr      varchar(8000)  --value to pad
)
RETURNS
varchar(8000)
AS

/*
TEST WITH:
----------
PRINT '     dbo.QuoteNull(null)             ->'+dbo.QuoteNull(null)+'<-'
PRINT '     dbo.QuoteNull(''apple'')          ->'+dbo.QuoteNull('apple')+'<-'
PRINT '     dbo.QuoteNull(123)              ->'+dbo.QuoteNull(123)+'<-'
PRINT '     dbo.QuoteNull(GETDATE())        ->'+dbo.QuoteNull(GETDATE())+'<-'
PRINT '     dbo.QuoteNull(GETDATE())        ->'+dbo.QuoteNull(CONVERT(varchar(23),GETDATE(),121))+'<-'
*/

BEGIN
    RETURN COALESCE(''''+@InputStr+'''','null')
END

GO

paste this into your code:

INSERT INTO YourLogTable
        (xxx,yyy,zzz,ColumnTextValue)
    SELECT
        xxx,yyy,zzz,'values:'
            +'  '+RTRIM('ColumnNameInt               ')+'='+dbo.QuoteNull(                    ColumnNameInt               )
            +', '+RTRIM('ColumnNameVarchar           ')+'='+dbo.QuoteNull(                    ColumnNameVarchar           )
            +', '+RTRIM('ColumnNameChar              ')+'='+dbo.QuoteNull(                    ColumnNameChar              )
            +', '+RTRIM('ColumnNameDate              ')+'='+dbo.QuoteNull(CONVERT(varchar(23),ColumnNameDate         ,121))
        FROM DELETED

make sure you have one row for each column in your table (if you have more just delete the extra ones later), if you want to see any dates in detail, use the convert as shown above.

run this query:

select sc.name 
    FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id 
    where UPPER(so.name)=UPPER('YourTableName') order by sc.colorder desc

take the output in SQL Server Management Studio (within text output mode), and do ALT-LEFT_Click-Drag a square over the column names, and copy this column based selection.

Go back to your code and ALT-Click-Drag a square over the complete "ColumnName..." values in the left column of the insert statement and paste. If you made a column selection, it will replace the column only and leave the code unchanged to the left and right. Do the same thing for the "ColumnName..." values in the right of the insert and you now have an INSERT that will build the data you want but will not waste too much time in the trigger.