declare @bit int ,
	@field int ,
	@char int,	
	@FName varchar(50),
	@ID int,
	@OldValue nvarchar(100),
	@NewValue nvarchar(100)

      set @field =0
	declare Cur cursor local for SELECT ID FROM inserted
	open Cur
	fetch Cur into @ID
	while (@@fetch_status=0)
	begin
        	while @field < (SELECT MAX(colid) FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'TestTable'))
		begin

			SET @field = @field + 1
			SET @bit = (@field - 1) % 8 + 1		
			SET @bit = power(2,@bit - 1)
			SET @char = ((@field - 1) / 8) + 1
			if (substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0)
			begin
				SELECT @FName = name FROM syscolumns WHERE colid = @field and id = (SELECT id FROM sysobjects WHERE name='TestTable')
				--getOldValue (from deleted)
				--getNewValue (from inserted)
				EXEC LogUpdate 'TestTable', @ID, @FName, @OldValue, @NewValue
			end
		end
		fetch Cur into @ID
	end
end

INSERT INTO dbo.MyAuditTable
SELECT CASE WHEN I.ID IS NULL AND D.ID IS NOT NULL THEN 'Deleted'
            WHEN I.ID IS NOT NULL AND D.ID IS NULL THEN 'Inserted'
            ELSE 'Updated'
            END as [Action],
       GetDate() as [AuditDate],
       MyTable.*
FROM   dbo.MyTable
       LEFT OUTER JOIN inserted I
              ON I.ID = MyTable.ID
       LEFT OUTER JOIN deleted D
              ON D.ID = MyTable.ID

use northwind
go

set nocount on

-- select * from orders

-- I use northwind..orders as example
-- You will be working with the inserted + deleted tables in the trigger.

-- Say this is the current field we are parsing...
declare @FName varchar(50)
set @FName = 'ShipName' -- 'ShipName' 'CustomerID' 'OrderDate', 

-- get the data into a temp table, use 1 row as example (which will be available in dynamic sql)
select top 1 * into #inserted from orders

-- need som variables :)
declare @dynamicsql nvarchar(1000)
declare @paramterdef nvarchar(1000)
declare @myvalue sql_variant

set @paramterdef = '@myvalue sql_variant output'
set @dynamicsql = 'select @myvalue=' + @FName + ' from #inserted'

-- execute the dynamic sql and retrieve the value of the field as an output parameter
exec sp_executesql @dynamicsql, @paramterdef, @myvalue output

-- your value is available here
select @myvalue as [dynamically retrieved value]

-- cleanup
drop table #inserted

set nocount on
--data table
create table junk 
	(junkid int identity(1,1) primary key
	,c1 varchar(10)
	,c2 varchar(10)
	,c3 varchar(10)
	,c4 varchar(10)
	,c5 varchar(10)
	,c6 int
	,c7 int
	,c8 int
	,c9 int
	,c10 int)
go

--log table
create table EditLog (
	EditLogId int identity(1,1) primary key
	,tableName varchar(30)
	,actionType varchar(6)
	,junkid int
	,ColName varchar(30) --big enough to 
	,OldValue varchar(30)
	,NewValue varchar(30)
	,updateBy varchar(30)
	,updateDate datetime)
go

--insert sample data
insert junk (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10)
select '1','1','1','1','1',1,1,1,1,1 union
select '2','2','2','2','2',2,2,2,2,2 union
select '3','3','3','3','3',3,3,3,3,3 

go
--trigger for UPDATE
create trigger trJunk on junk for update 
as
begin
	insert EditLog 
		(tablename
		,ActionType
		,junkid
		,colName
		,oldValue
		,newValue
		,updateBy
		,updateDate)
	select	'junk'
		,'Update'
		,i.junkid
		,c.Column_Name
		,OldValue = case 
			when ordinal_position = 2 then convert(varchar(10),d.c1)
			when ordinal_position = 3 then convert(varchar(10),d.c2)
			when ordinal_position = 4 then convert(varchar(10),d.c3)
			when ordinal_position = 5 then convert(varchar(10),d.c4)
			when ordinal_position = 6 then convert(varchar(10),d.c5)
			when ordinal_position = 7 then d.c6
			when ordinal_position = 8 then d.c7
			when ordinal_position = 9 then d.c8
			when ordinal_position = 10 then d.c9
			when ordinal_position = 11 then d.c10
			end
		,NewValue = case 
			when ordinal_position = 2 then convert(varchar(10),i.c1)
			when ordinal_position = 3 then convert(varchar(10),i.c2)
			when ordinal_position = 4 then convert(varchar(10),i.c3)
			when ordinal_position = 5 then convert(varchar(10),i.c4)
			when ordinal_position = 6 then convert(varchar(10),i.c5)
			when ordinal_position = 7 then i.c6
			when ordinal_position = 8 then i.c7
			when ordinal_position = 9 then i.c8
			when ordinal_position = 10 then i.c9
			when ordinal_position = 11 then i.c10
			end
		,system_user
		,getdate()
	from	inserted i
	join	deleted d
			on i.junkid = d.junkid

	--cross join to column list (one row for each column)
	cross join information_schema.columns c
	where	c.table_name = 'junk'

	--exclude the identityColumn
	and	c.ordinal_position > 1

	--only the columns updated
	and	substring(columns_updated(), (c.ordinal_position/8)+1, 1)
		& power(2, (c.ordinal_position-1)%8) > 0
end
go


--Perform some updates
update junk set 
	c2 = '10'
	,c8 = 10 
where junkid in (1,2)


update junk set 
	c1 = '11'
	,c2 = '11'
	,c10 = 11
where junkid in (3)

--check the log table
select * from EditLog
go

--clean up junk
drop trigger trJunk
go
drop table editLog
go
drop table junk
go

create table x(a int, b int, c int)
go

select colid,name from syscolumns where id = object_id('x')

alter table x drop column b
go

select colid,name from syscolumns where id = object_id('x')

drop table x

CREATE TRIGGER TestTable_UpdateT ON [dbo].[TestTable] 
FOR UPDATE
AS

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@oldvalue varchar(1000),
@newvalue varchar(1000),
@ID int,
@TableName varchar(128) ,
@sql varchar(2000),
@IsOk int

begin

	create table #Audit (FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), ID int)
	
	select * into #inserted from inserted
	select * into #deleted from deleted
	
	set @TableName = 'TestTable'
	

	select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
	while @field < @maxfield
	begin
		select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
		select @bit = (@field - 1 )% 8 + 1
		select @bit = power(2,@bit - 1)
		select @char = ((@field - 1) / 8) + 1
		if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
		begin

			select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
			if (dbo.GetDatatype4Field(@TableName,@fieldname) in ('binary','varbinary','char','varchar','nchar','nvarchar','datetime','smalldatetime','decimal','numeric','float','real','bigint','int','smallint','tinyint','bit'))
			begin
				set @sql = 'INSERT INTO #Audit (FieldName,OldValue,NewValue,ID)  
						SELECT ''#FIELDNAME#'' as FieldName, convert(varchar(1000),d.#FIELDNAME#) as OldValue, convert(varchar(1000),i.#FIELDNAME#) as NewValue, i.ID as ID
						FROM #inserted i full outer join #deleted d on i.ID = d.ID'
				set @sql = REPLACE(@sql,'#FIELDNAME#',@fieldname)
				exec (@sql)
			end
		end
	end

	declare Cur cursor local for select FieldName, OldValue, NewValue, ID FROM #Audit
	open Cur
	fetch Cur into @fieldname, @OldValue, @NewValue, @ID
	while (@@fetch_status=0)
	begin
		exec LogUpdate @TableName, @ID, @fieldname, @OldValue, @NewValue
		fetch Cur into @fieldname, @OldValue, @NewValue, @ID
	end

	close Cur

	drop table #Audit
	drop table #inserted
	drop table #deleted
end

CREATE FUNCTION dbo.GetDatatype4Field (@Table varchar(255), @Field varchar(255))
RETURNS varchar(50) AS  
BEGIN 
	declare @returnValue varchar(50)
	select @returnValue = DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where lower(TABLE_NAME) = lower(@Table) and lower(COLUMN_NAME)=lower(@Field)
	return @returnValue
END

quote:

---

Originally posted by Kristen

"I don't want to administer hundreds of log tables by hand"

Me neither ... all of our "Auditing Triggers" are mechanically generated - i.e. a couple of SQL statements will create them for all appropriate tables.

Kristen

---