[SQL]不要偷刪除我的資料啦 !
既上次放個陷阱來抓偷改資料庫的傢伙後 (詳見 : [SQL]不要偷改我的資料庫啦 ! )，果然風平浪靜了一陣子，正想要順利結案之際，客戶端又忽然冒出一個問題了，某些資料表的資料會無故不見了，懷疑是我們程式沒有正常寫入，因此又衍生出另外一個案外案出來了。
為了針對那些 Table，原本想說開個 SQL Profile 來攔截就好了，但因為又怕影響 Server 端的效能，因此就做個簡單的 Trigger 來追蹤囉。首先先建立一個來存放紀錄的 Table
CREATE TABLE [dbo].[_sessioninfo]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [host_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [login_name] [nvarchar](128) NULL, [nt_user_name] [nvarchar](128) NULL, [process_time] [datetime] NULL DEFAULT (getdate()), ) ON [PRIMARY]
接下來我在要稽核的 Table 上面建立一組 Trigger
CREATE TRIGGER dbo.T1_Delete_Trigger ON dbo.T1 AFTER DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO dbo._sessioninfo ( host_name,program_name,login_name,nt_user_name ) SELECT host_name,program_name,login_name,nt_user_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID END GO
以這個範例來說，我在 T1 這個 Table 上面建立一 個 DELETE 事件的 Trigger，配合 @@SPID 取得命令的 Session ID 之後，透過 dm_exec_sessions 這個 DMV 來取得該 Session 連線的資訊，並且將這些資訊存放到 _sessioninfo 這個 Table 內。因此只要對這個 Table 刪除資料的時候，我們就會知道這個命令是從哪台電腦上使用哪個帳號登入 SQL Server 所下的指令。