用觸發器生成數據庫表的數據操作日志

作為一名數據庫管理員，你盡力以各部門熟知的不同格式，向各部門提供它們所需要的數據。你通常將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')