[SQL]不要偷改我的資料庫啦 !
似乎客戶會發生問題的時間點都是在要下班的前夕，今天也不例外地又有一樁離奇事件又產生了。客戶抱怨他們家的資料庫中特定的幾個 Table，會平白無故地的消失 PK，造成資料會有重複的狀況，每次把 PK 重新設定上去之後，隔幾天又消失不見了，於是打電話來問乖乖要買甚麼顏色的 要求我們要幫忙找出兇手。
連線查看一下客戶的環境，不論是防火牆或者是資料庫內的相關設定都沒有問題，原本想偷懶使用 SQL Profile，但實在擔心這個方式耗用的主機資源太多；而客戶所使用的資料庫版本又只是 SQL Server 2008 Standard 版本，也沒有辦法使用 SQL Audit 來做稽核。因此就想到使用 DDL Triggers 來實作這個部分。
大家往往在學習資料庫設計的時候，都會談到 TRIGGER ( 觸發程序 )的使用，而多半人都會知道(也認為)觸發程序一定是綁訂在 Table 上面。這個觀念並沒有錯誤，但是在 SQL Server 2000 之後，不只是 TABLE 上面可以設定 TRIGGER，還可以綁定在 VIEW 上面，這一部分可以參考 MSDN 上面的相關說明 ( http://technet.microsoft.com/zh-tw/library/ms189799(v=sql.90).aspx )；而在 SQL Server 2005 之後，又多增加一種特殊類型的觸發程序，會在回應「資料定義語言」(Data Definition Language，DDL) 命令發生時引發，因此我們就可以在這裡做紀錄亦或者 ROLLBACK 整個處理程序。
DDL TRIGGER 還可以細分 Server 層級和資料庫層級的兩種 ( 如下表所示 ) :
.gif)
( 取自 http://msdn.microsoft.com/zh-tw/ms191441.aspx )
因為現在所遇到的狀況是發生要去紀錄 TABLE INDEX 有改變的相關資訊，因此我會先在資料庫內建立一個 TABLE 來存放紀錄，因為擔心紀錄的資訊不夠完整，因此我在外後一個欄位把事件發生的整個 XML 也一併紀錄下來，避免有甚麼資訊沒有記錄到的時候還可以利用那個欄位找出相關資訊。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_DDLEventLog]') AND type in (N'U'))
DROP TABLE [dbo].[_DDLEventLog]
GO
CREATE TABLE [dbo].[_DDLEventLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [sysname] NULL,
[ObjectType] [sysname] NULL,
[ObjectName] [sysname] NULL,
[UserName] [sysname] NULL,
[CommandText] [varchar](max) NULL,
[EventData] [xml] NULL,
CONSTRAINT [PK__DDLEventLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO
建立好之後，我們就要來實作 DDL TRIGGER，但因為擔心資訊太多，因此我只記錄目前會有異常狀況發生的幾個 TABLE ，剛好這些名稱都是是 MOC 開頭的，您也可以按照你的狀況作適當的的調整：
CREATE TRIGGER [DDL_TRIGGER_FOR_MOC]
ON DATABASE
FOR ALTER_TABLE,DROP_INDEX,ALTER_INDEX
AS
SET NOCOUNT ON
DECLARE @DATA XML
DECLARE @TABLENAME SYSNAME
SET @DATA = EVENTDATA()
SET @TABLENAME = CONVERT(SYSNAME, @DATA.query('data(/EVENT_INSTANCE/ObjectName)'))
IF LEFT( @TABLENAME ,3 )= 'MOC'
BEGIN
INSERT INTO dbo._DDLEventLog( EventTime, EventType, ObjectType, ObjectName, UserName, CommandText, [EventData] )
SELECT GETDATE(), CONVERT(SYSNAME, @DATA.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(SYSNAME, @DATA.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(SYSNAME, @DATA.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(SYSNAME, @DATA.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @DATA.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')),
@DATA;
END
GO
而如果這中間有需要暫時關閉或者是重新啟動這個 TRIGGER 的話，也可以透過以下的指令去處理
-- 關閉
DISABLE TRIGGER [DDL_TRIGGER_FOR_MOC] ON DATABASE
-- 啟用
ENABLE TRIGGER [DDL_TRIGGER_FOR_MOC] ON DATABASE
這樣陷阱就做好了，剩下的就等時間看何時會有獵物掉進來囉。