Trigger problem

[sssdexter]

Hi,

I got a table which have 30 fields, sometime user could just update 1 or 2 fields for the respective record. I would like to write a trigger that when user update the table, only get those edited fields new value and insert it into another table, for example log table.

How could i get the new update columns name and it's new entered value?

Thanks a lot.

 

[SamGarland]

Try using this code. It will work on a table called MyTable with an identity field called id. It should log the id value along with new values for the columns that have been affected by the update statement that fired the trigger. I had to create a temporary table to duplicate the inserted table because I couldn't access the inserted pseudotable when using dynamic SQL. If anyone has any improvements on this, please let me know.

CREATE TRIGGER log_MyTable_update_trigger
ON MyTable
FOR UPDATE
AS
BEGIN
DECLARE @col_num INT
DECLARE @col_name VARCHAR(30)

SET @col_num = 0
SET @col_name = COL_NAME(OBJECT_ID('MyTable'), @col_num + 1)

WHILE @col_name <> 'NULL'
BEGIN
IF SUBSTRING(COLUMNS_UPDATED(),@col_num/8+1,1)=power(2,@col_num%8)
BEGIN
SELECT * into #temp from inserted
EXEC('INSERT INTO updates SELECT ''ID:''+CAST(id as VARCHAR(200))+'' ' +
@col_name +
':''+CAST(' +
@col_name +
' as VARCHAR(200)) FROM #temp' )
DROP TABLE #temp
END

SET @col_num = @col_num + 1
SET @col_name = COL_NAME(OBJECT_ID('MyTable'), @col_num + 1)
END
END