How to output old and new value?

Question

SQL

it is necessary to receive in the update trigger old and new value and to write down in other table.
I know that these values ​​can be taken from the inserted and deleted tables, but how do I know which fields have changed through columns_updated (index of each field/column)? and what to do if the number of columns in the table is more than 8?

Answer 1

via UPDATE()
https://msdn.microsoft.com/ru-ru/library/ms187326%...
for example:

-- drop table test
CREATE TABLE test (id INT IDENTITY(1,1) NOT NULL, t1 INT, t2 INT, t3 INT);
GO

INSERT INTO test (t1, t2, t3)
VALUES (1,2,0),(3,4,0),(5,6,1)
GO

CREATE TRIGGER TG_testModify ON test AFTER INSERT,UPDATE
AS
BEGIN

    IF UPDATE([t1])  
        SELECT d.t1 AS t1_old, i.t1 AS t1_new, 'Column_t1' 
              FROM INSERTED AS i
                    LEFT JOIN DELETED AS d ON i.id = d.id
        WHERE ( NULLIF(d.t1, i.t1) IS NOT NULL AND d.t1 IS NOT NULL )
               OR ( NULLIF(i.t1, d.t1) IS NOT NULL AND d.t1 IS NULL )
END
GO

UPDATE test SET t1 = 4 WHERE t3 = 0;
UPDATE test SET t1 = NULL WHERE t3 = 1;
UPDATE test SET t1 = 2 WHERE t3 = 1;