SQL Trigger 來紀錄哪些欄位被修改了

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

C# DateTime 相減

每次用到都要去 Google，這次決定把他記下來

當兩個 DateTime 要相加減時就需要用到 TimeSpan

DateTime begDate = DateTime.Now;
TimeSpan ts = DateTime.Now - begDate;
string diff = ts.Minutes.ToString() + "分" + ts.Second.ToString() + "秒";

Transaction Log 備份失敗 - 因為沒有目前的資料庫備份

昨天突然 Transaction Log 備份出現了錯誤：因為沒有目前的資料庫備份。 BACKUP LOG 正在異常結束。

只好求助 Google 了，才知道原來是當時的資料庫的復原模式重簡單改成完整之後都沒有進行過完整備份或差異備份，所以 Transaction Log 沒有一個紀錄點，才會有這個錯誤發生。



Reference: https://dotblogs.com.tw/terrychuang/2013/08/28/115722