• user who made change
• table that was changed
• column changed
• original value
• new value

CREATE TRIGGER yourtrigger ON yourtable
AFTER INSERT,UPDATE 
AS
BEGIN
INSERT INTO Log_Table (table_name,user_name,modified_date,column_changed,original_value,newvalue)
SELECT 'YourTable',
       SUSER_SNAME(),
       GETDATE(),
       CASE WHEN i.col1<>COALESCE(d.col1.'') THEN 'col1' ELSE NULL END,
d.col1,i.col1,CASE WHEN i.col2<>COALESCE(d.col2.'') THEN 'col2' ELSE NULL END,
d.col2,i.col2
...
FROM INSERTED 1
LEFT JOIN DELETED d
ON i.PK=d.PK
END

quote:

---

Originally posted by im1dermike

Thanks, visakh16.

I understand most of what you entered. My only issue is your code that gets the original and new values. What exactly are you doing there? Do you have a static case statement for each column to determine which columns' original and new values are different? Can you delve into that part of your example a little more and/or give another example?

Thanks

---

set nocount on
--Auto archive script
--blindman, 3/2/2006
--blindman, 11/7/2007:	Enhanced to handle inserts/updates/deletes

--creates a script to create archive tables and triggers for saving deleted records.

declare	@TableNameMask varchar(50)
declare	@ArchiveSuffix varchar(50)
declare	@DeletedColumnName varchar(50)
set	@TableNameMask = '' --Filters specific tables by name.
set	@ArchiveSuffix = '_arc' --Suffix to append to table names.
set	@DeletedColumnName = 'DeletedFlag'  --Will indicate whether archive record was the result of a delete operation.

declare	@TableName varchar(500)
declare	@FieldDefs varchar(max)
declare	@FieldList varchar(max)
declare	@DeletedList varchar(max)
declare	@SQLStringTABLECREATE varchar(max)
declare	@SQLStringTRIGGERHEADER varchar(max)
declare	@SQLStringTRIGGERINSERT varchar(max)
declare	@SQLStringTRIGGERDELETE varchar(max)
declare	@KeyList varchar(max)
declare	@FirstKey varchar(100)


/*
select '/*'
select	name as 'The following tables will be scripted:'
from	sysobjects
where	type = 'U'
	and name not like 'dt%'
	and name not like '%' + @ArchiveSuffix
	and name not like '%staging%'
order by name
select '*/'
*/

declare	TableList cursor for
	select	name
	from	sysobjects
	where	type = 'U'
		and (name like @TableNameMask or nullif(@TableNameMask, '') is null)
		and name not like 'dt%'
		and name not like '%' + @ArchiveSuffix
		and name not like '%staging%'
		and name not in ('sysdiagrams')
	order by name

open TableList
fetch next from TableList into @TableName

while @@fetch_status = 0
begin
	set @FieldDefs = null
	set @FieldList = null
	set @DeletedList = null
	set @SQLStringTABLECREATE = null
	set @SQLStringTRIGGERHEADER = null
	set @SQLStringTRIGGERINSERT = null
	set @SQLStringTRIGGERDELETE = null
	set @KeyList = null
	set @FirstKey = null

	select	@FieldDefs = isnull(@FieldDefs + ',' +char(10) + '	', '')
		+ syscolumns.name
		+ 	' ' + systypes.name
				+ isnull(' (' + case systypes.name
						when 'timestamp' then Null
						when 'smalldatetime' then Null
						when 'datetime' then Null
						when 'int' then Null
						when 'bigint' then Null
						when 'smallint' then Null
						when 'tinyint' then Null
						when 'real' then Null
						when 'bit' then Null
						when 'money' then Null
						when 'uniqueidentifier' then Null
						when 'xml' then Null
						when 'decimal' then cast(syscolumns.prec as varchar(10)) + ', ' + cast(syscolumns.scale as varchar(10))
						when 'numeric' then cast(syscolumns.prec as varchar(10)) + ', ' + cast(syscolumns.scale as varchar(10))
						else case when syscolumns.length = -1 then 'max' else cast(syscolumns.length as varchar(10)) end
						end + ')', '')
			 	+ ' ' + case syscolumns.isnullable when 0 then 'NOT NULL' else 'NULL' end
	from	syscolumns
		inner join sysobjects on syscolumns.id = sysobjects.id
		inner join systypes on syscolumns.xusertype = systypes.xusertype
	where	sysobjects.name = @TableName
		and systypes.name not in ('text', 'ntext', 'image') --These cannot be referenced in the deleted table
		and syscolumns.name not in ('Modified', 'Modifier')
	order by syscolumns.colid

	select	@FieldList = isnull(@FieldList + ',' +char(10) + '	', '')
		+ syscolumns.name
	from	syscolumns
		inner join sysobjects on syscolumns.id = sysobjects.id
		inner join systypes on syscolumns.xusertype = systypes.xusertype
	where	sysobjects.name = @TableName
		and systypes.name not in ('text', 'ntext', 'image') --These cannot be referenced in the deleted table
		and syscolumns.name not in ('Modified', 'Modifier')
	order by syscolumns.colid

	select	@DeletedList = isnull(@DeletedList + ',' +char(10) + '	', '')
		+ 'deleted.' + syscolumns.name
	from	syscolumns
		inner join sysobjects on syscolumns.id = sysobjects.id
		inner join systypes on syscolumns.xusertype = systypes.xusertype
	where	sysobjects.name = @TableName
		and systypes.name not in ('text', 'ntext', 'image') --These cannot be referenced in the deleted table
		and syscolumns.name not in ('Modified', 'Modifier')
	order by syscolumns.colid

	select	@KeyList = isnull(@KeyList + char(10) + '	and ', '') + 'deleted.' + columns.name + ' = inserted.' + columns.name
	from	sys.objects objects
		inner join sys.indexes indexes on objects.object_id = indexes.object_id
		inner join sys.index_columns index_columns
		    on indexes.object_id = index_columns.object_id
		    and indexes.index_id = index_columns.index_id
		inner join sys.columns columns
		    on index_columns.object_id = columns.object_id
		    and index_columns.column_id = columns.column_id
	where	is_primary_key = 1
		and objects.name = @TableName

	set @FirstKey = 
	    (select	top 1 columns.name
	    from	sys.objects objects
		    inner join sys.indexes indexes on objects.object_id = indexes.object_id
		    inner join sys.index_columns index_columns
			on indexes.object_id = index_columns.object_id
			and indexes.index_id = index_columns.index_id
		    inner join sys.columns columns
			on index_columns.object_id = columns.object_id
			and index_columns.column_id = columns.column_id
	    where	is_primary_key = 1
		    and objects.name = @TableName
	    order by columns.column_id)

	set	@SQLStringTABLECREATE = '--<<[' + @TableName + ']>>--' + char(10)
		+ 'Create table [' + @TableName + @ArchiveSuffix + ']' + char(10)
		+ '	(' + @FieldDefs + ',' + char(10)
		+ '	' + @DeletedColumnName + ' bit NOT NULL,' + char(10)
		+ '	Modified datetime NULL,' + char(10)
		+ '	Modifier varchar (50) NULL)' + char(10)
		+ 'GO' + char(10)
		+ char(10)
		+ 'ALTER TABLE [' + @TableName + @ArchiveSuffix + '] ADD ' + char(10)
		+ '	CONSTRAINT [DF_' + @TableName + @ArchiveSuffix + '_Deleted] DEFAULT (0) FOR [' + @DeletedColumnName + '],' + char(10)
		+ '	CONSTRAINT [DF_' + @TableName + @ArchiveSuffix + '_Modified] DEFAULT (getdate()) FOR [Modified],' + char(10)
		+ '	CONSTRAINT [DF_' + @TableName + @ArchiveSuffix + '_Modifier] DEFAULT (suser_sname()) FOR [Modifier]' + char(10)
		+ 'GO' + char(10)

	set	@SQLStringTRIGGERHEADER ='CREATE TRIGGER [TR_' + @TableName + '_Archiver] ON [' + @TableName + ']' + char(10)
		+ 'FOR INSERT, UPDATE, DELETE' + char(10)
		+ 'AS' + char(10)
		+ 'set nocount on' + char(10)
		+ 'if exists (select * from dbo.sysobjects where id = object_id(N''[' + @TableName + @ArchiveSuffix + ']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)' + char(10)
		+ 'begin'

	set	@SQLStringTRIGGERINSERT = 'insert into [' + @TableName + @ArchiveSuffix + ']' + char(10)
		+ '	(' + @FieldList + ',' + char(10)
		+ '	' + @DeletedColumnName + ')' + char(10)
		+ 'select	' + @FieldList + ',' + char(10)
		+ '	0' + char(10)
		+ 'from	inserted' + char(10)
		+ 'where	TRIGGER_NESTLEVEL() = 1' + char(10)

	set	@SQLStringTRIGGERDELETE = 'insert into [' + @TableName + @ArchiveSuffix + ']' + char(10)
		+ '	(' + @FieldList + ',' + char(10)
		+ '	' + @DeletedColumnName + ')' + char(10)
		+ 'select	' + @DeletedList + ',' + char(10)
		+ '	1' + char(10)
		+ 'from	deleted' + char(10)
		+ 'left outer join inserted on ' + @KeyList + char(10)
		+ 'where inserted.' + @FirstKey + ' is null' + char(10)
		+ 'end' + char(10)
		+ 'GO'

	if @FirstKey is not null
	    begin
	    print	@SQLStringTABLECREATE
	    print	@SQLStringTRIGGERHEADER
	    print	@SQLStringTRIGGERINSERT
	    print	@SQLStringTRIGGERDELETE
	    end
	else select '--NO PRIMARY KEY ON TABLE ' + @TableName + '!'

	fetch next from TableList into @TableName
end

close tablelist
deallocate tablelist

quote:

---

Originally posted by im1dermike

Well, yes I would have real problems with regard to your functionality, but surely tables can exist that don't have a primary key.

---