作為一名數據庫管理員，你盡力以各部門熟知的不同格式，向各部門提供它們所需要的數據。你通常將MS Excel格式的數據遞交到會計部門，或將數據以Html報表的形式呈現給普通用戶。你們的系統安全管理員們則習慣於用文本閱讀器或者事件查看器來查看日志。本文將介紹如何使用觸發器，把DML（數據操作語言）對數據庫中的特定數據表的改動記錄下來。注：下列例子為Insert型觸發器，不過改成Delete／Update型的觸發器也很容易。
操作步驟首先讓我們在Northwind數據庫內創建一個簡單表。
create table tablefortrigger ( track int identity(1,1) primary key, Lastname varchar(25), Firstname varchar(25) )
創建好這個數據表後，添加一個標准message到master數據庫的sysmessages數據表中。注意，我所添加的是一個參變量，用以接受一個字符值，它將被輸出顯示給管理員們。通過設置@_with_log參數為true，我們包管相關結果被發送到事件日志。
sp_addmessage 50005, 10, '%s', @with_log = true
現在我們創建這條用有意義的信息填充的消息。下面的信息將填充這條消息，並且記錄到文件中：
·操作的類型（插入）。
·受到影響的數據表。
·改動的日期與時間。
被該語句插入的全部字段。 下面的這個觸發器用預定義值（1～3個字符）創建一個字符串，該預定義值位於inserted數據表中。（這個inserted數據表駐留在內存中，它容納被插入到觸發器所在數據表的記錄行）。觸發器連接這些值並放到一個@msg變量。然後這個變量被傳送到raiserror函數，該函數將它寫到事件日志中。
Create trigger TestTrigger on tablefortrigger for insert as --聲明儲存消息的變量 Declare @Msg varchar(8000) --將"操作／表名／日期時間／插入字段"賦與消息 set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | ' +(select convert(varchar(5), track) + ', ' + lastname + ', ' + firstname from inserted) --產生錯誤發送給事件查看器。 raiserror( 50005, 10, 1, @Msg)
運行以下語句對觸發器進行測試，然後查看事件日志：
Insert into tablefortrigger(lastname, firstname) Values('Doe', 'John')
如果你打開事件日志，你應該看到以下消息：
作為一名數據庫管理員，你盡力以各部門熟知的不同格式，向各部門提供它們所需要的數據。你通常將MS Excel格式的數據遞交到會計部門，或將數據以Html報表的形式呈現給普通用戶。你們的系統安全管理員們則習慣於用文本閱讀器或者事件查看器來查看日志。本文將介紹如何使用觸發器，把DML（數據操作語言）對數據庫中的特定數據表的改動記錄下來。注：下列例子為Insert型觸發器，不過改成Delete／Update型的觸發器也很容易。
操作步驟首先讓我們在Northwind數據庫內創建一個簡單表。
create table tablefortrigger ( track int identity(1,1) primary key, Lastname varchar(25), Firstname varchar(25) )
創建好這個數據表後，添加一個標准message到master數據庫的sysmessages數據表中。注意，我所添加的是一個參變量，用以接受一個字符值，它將被輸出顯示給管理員們。通過設置@_with_log參數為true，我們包管相關結果被發送到事件日志。
sp_addmessage 50005, 10, '%s', @with_log = true
現在我們創建這條用有意義的信息填充的消息。下面的信息將填充這條消息，並且記錄到文件中：
·操作的類型（插入）。
·受到影響的數據表。
·改動的日期與時間。
被該語句插入的全部字段。 下面的這個觸發器用預定義值（1～3個字符）創建一個字符串，該預定義值位於inserted數據表中。（這個inserted數據表駐留在內存中，它容納被插入到觸發器所在數據表的記錄行）。觸發器連接這些值並放到一個@msg變量。然後這個變量被傳送到raiserror函數，該函數將它寫到事件日志中。
Create trigger TestTrigger on tablefortrigger for insert as --聲明儲存消息的變量 Declare @Msg varchar(8000) --將"操作／表名／日期時間／插入字段"賦與消息 set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | ' +(select convert(varchar(5), track) + ', ' + lastname + ', ' + firstname from inserted) --產生錯誤發送給事件查看器。 raiserror( 50005, 10, 1, @Msg)
運行以下語句對觸發器進行測試，然後查看事件日志：
Insert into tablefortrigger(lastname, firstname) Values('Doe', 'John')
如果你打開事件日志，你應該看到以下消息：
既然我們已經有辦法寫入事件日志了，那麼讓我們修改一下觸發器，將數據寫到一個文本文件中。這次改動還須添加另一個變量@CmdString，以及使用擴展儲存過程xp_cmdshell。 因為我們要寫入文件系統，安全權限開始有影響了。所以，執行插入操作的用戶必須具備該文本文件的讀寫權限。因此，設計一個C／S結構的應用程序供多用戶運行，或許不是一個可行的解決方案。更合理的方案是，設計一個三層應用程序，由你的中間層組件對單用戶數據庫進行調用。在後一個方案中，對那個文本文件的權限管理其實比管理一個用戶還容易。
Alter trigger TestTrigger on tablefortrigger for insert as Declare @Msg varchar(1000) --儲存將由xp_cmdshell執行的命令 Declare @CmdString varchar (2000) set @_msg = ' insert | tablefortrigger | ' + convert ( varchar ( 20 ) , getdate ( ) ) + ' | ' + ( select convert ( varchar ( 5 ) , track ) + ' , ' + lastname + ' , ' + firstname from insert ) - [99%]set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | ' + (select convert(varchar(5), track) + ', ' + lastname + ', ' + firstname from inserted) --產生錯誤發送給事件查看器。 raiserror( 50005, 10, 1, @Msg) set @CmdString = 'echo ' + @Msg + ' >> C:logtest.log' --寫到文本文件 exec master.dbo.xp_cmdshell @CmdString
讓我們對它進行測試，先運行前面的插入語句，然後打開C:logtest.log文件查看結果：
Insert into tablefortrigger(lastname, firstname) Values('Doe', 'John')
問題解決了，對不對？哦，還沒完全解決。發生多次重復插入的事件是什麼原因？在這個例子中，你必須分別地處理每條記錄。為了達到這個目的，我們必須用一個會帶來麻煩的游標來訪問"隱蔽面"。在執行以前，我必須預先給予警告。你應當了解的是，當這個應用程序進行大規模地記錄插入、更新或刪除時要當心，因為它可能會耗費大量的內存。
像你從下面看到的一樣，這次我們在前面那個例子的基礎上稍加調整，引入了一個游標，對該插入表的全部記錄進行循環讀取。每條記錄分別插入一條線條，將各個事件區分開來。
ALTER trigger TestTrigger on tablefortrigger for insert as Declare @Msg varchar(1000) Declare @CmdString varchar (1000) Declare GetinsertedCursor cursor for Select 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | ' + convert(varchar(5), track) + ', ' + lastname + ', ' + firstname from inserted open GetinsertedCursor Fetch Next from GetinsertedCursor into @Msg while @@fetch_status = 0 Begin raiserror( 50005, 10, 1, @Msg) Fetch Next from GetinsertedCursor into @Msg set @CmdString = 'echo ' + @Msg + ' >> C:logtest.log' exec master.dbo.xp_cmdshell @CmdString End close Getinsertedcursor deallocate GetInsertedCursor
現在讓我們執行重復多次插入測試：
Insert into tablefortrigger(lastname, firstname) Select lastname, firstname from employees
結論
在繼續完成之前，有些人認為必須考慮性能與安全問題。你將看到寫入文本文件的開銷，而對於一個每分鐘處理5000項事務的數據庫來說，這樣大的開銷也許不可接受。由於xp_cmdshell是在SQL外操作的，寫入到文件的錯誤不會回滾事務。倘若入侵者使用一個隱蔽的途徑來改變你的數據，這個事件不會被登記到那個文本文件中。不過事件日志將記錄該次DML改動。作為一次最好的實踐，各事件的編號應該被用於對照日志文件的各行記錄，以便發現所有的差異。
有很多種方法可以達到本文目標，上述腳本也可以有許多的變化。我希望你能接受這個腳本，然後作出改進並提出建議，使它更有效率。
