Reading inserted column names and values in a TSQL trigger

Question

I've been asked to create history tables for every table in a database. Then create a trigger that will write to the history table whenever the primary table is updated.

The history tables have the same structure as the primary table, but with a couple of extra rows ('id' and 'update type')

I've never done anything with triggers before, but I would like to do is dynamically go through the columns in 'Inserted' and construct an insert statement to populate the history table.

However I cannot work out how to read the names of the columns and their individual values.

My half finished trigger currently looks like...

CREATE TRIGGER tr_address_history
ON address
FOR UPDATE
AS

DECLARE @colCount int
DECLARE @maxCols int
SET @colCount = 0
SET @maxCols = (SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted')

PRINT 'Number of columns = ' + CONVERT(varChar(10),@maxCols)
WHILE (@colCount <= @maxCols)
BEGIN
    DECLARE @name varchar(255)
    SELECT @name = column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'
    DECLARE @value varchar(255)
    SELECT @value = @name FROM Inserted

    PRINT 'name = ' + @name + ' and value = ' + @value
    SET @colCount = @colCount + 1
END
PRINT 'Done';

When the trigger runs it just says "Number of columns = 0"

Can anyone tell me what's wrong with :

SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'

Thanks...

Answer 1

The 'inserted' table is a pseudo-table; it doesn't appear in INFORMATION_SCHEMA.

There is the UPDATE() operator for use in triggers:

CREATE TRIGGER trigger_name ON tablename
FOR UPDATE
AS
SET NOCOUNT ON
IF (UPDATE(Column1) OR UPDATE(Column2))
BEGIN
  your sql here
END

COLUMNS_UPDATED

UPDATE()