SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_TestTable_Modify] on [dbo].[TestTable]
AFTER UPDATE
AS
BEGIN
DECLARE @DBTableID int;
DECLARE @Columnid int;
DECLARE @ColumnCount int;
DECLARE @i int;
DECLARE @ColumnName varchar(20);
DECLARE @SQL varchar(4000);
DECLARE @LogTime datetime;
DECLARE @program nvarchar(200)
SELECT @DBTableID=id from sysobjects where name='TestTable'
SELECT @ColumnCount=MAX(column_id) FROM sys.columns WHERE [object_id]=@DBTableID
SELECT @program=program_name from sys.dm_exec_sessions where session_id=@@SPID
select * into #inserted from inserted;
select * into #deleted from deleted;
SET @i = 0
SET @LogTime=GETDATE()
WHILE @i < @ColumnCount
BEGIN
SET @i = @i+1
SELECT @ColumnName=name FROM sys.columns WHERE [object_id]=@DBTableID AND column_id=@i
SET @SQL = 'INSERT INTO LogTable SELECT a.invno,''‘TestTable'',''' + @ColumnName + ''',a.' + @ColumnName + ',b.'+ @ColumnName + ',''' + convert(varchar(30), @LogTime,121) + ''',''' + @program + ''' FROM #deleted a,#inserted b WHERE a.PK=b.PK AND a.' + @ColumnName + ' <> b.' + @ColumnName
EXEC (@SQL)
END
drop table #inserted;
drop table #deleted;
END
GO
DateTime begDate = DateTime.Now;
TimeSpan ts = DateTime.Now - begDate;
string diff = ts.Minutes.ToString() + "分" + ts.Second.ToString() + "秒";
SELECT DISTINCT * FROM [TableName]SELECT DISTINCT * INTO [Temp_TableName] FROM [TableName]
DELETE FROM [TableName]
INSERT INTO [TableName] SELECT * FROM [Temp_TableName]
DROP TABLE [Temp_TableName]
