set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter trigger [tri3] on [dbo].[stu]
after insert --,update,delete
as
declare @intCountColumn int,
@intColumn int,
@actionId varchar(36),
@tableName varchar(10),
@columnName varchar(10),
@oldValue varchar(10),
@newValue varchar(10),
@modDate datetime,
@sqlCommand nvarchar(1000)
begin
select @tableName = 'stu'
select @intColumn=1
select @intCountColumn=count(ordinal_position)
from Information_Schema.Columns
where Table_name=@tableName
select @modDate = getDate()
select @actionId = NEWID()
--inserted
while @intColumn<=@intCountColumn
BEGIN
select * into #t from inserted
select @columnName = Col_Name(Object_ID(@tableName),@intColumn)
select @sqlCommand ='select @newValue= '+@columnName+' from #t'
execute sp_executesql @sqlCommand, N'@newValue varchar(36) output', @newValue output
--print @newVvalue
INSERT history (ACTIONID,TABLENAME,COLUMNNAME,NEWVALUE,moddate)
SELECT @actionId,@tableName,@columnName,@newValue,@modDate
--print 'Column (' + Cast(@intColumn as varchar)+')'+Col_Name(Object_ID('stu3'),@intColumn)+' has been changed'
drop table #t
set @intColumn = @intColumn+1
END
end