本文主要 涉及DDL觸發(fā)器和登錄觸發(fā)器的應(yīng)用實(shí)例。

MicrosoftSQL Server 提供兩種主要機(jī)制來強(qiáng)制使用業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。觸發(fā)器為特殊類型的存儲(chǔ)過程，可在執(zhí)行語言事件時(shí)自動(dòng)生效。SQL Server 包括三種常規(guī)類型的觸發(fā)器：DML 觸發(fā)器、DDL 觸發(fā)器和登錄觸發(fā)器。

1、當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語言 (DML) 事件時(shí)將調(diào)用 DML 觸發(fā)器。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。

關(guān)于DML觸發(fā)器應(yīng)用最為廣泛。這里不再贅述。MSDN官方說明:

2、當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語言 (DDL) 事件時(shí)將調(diào)用 DDL 觸發(fā)器。DDL 觸發(fā)器是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言 (DDL) 語句時(shí)觸發(fā)。它們可以用于在數(shù)據(jù)庫(kù)中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫(kù)操作。

下面我們用舉例說明DDL觸發(fā)器（%28SQL.90%29.aspx）的應(yīng)用：

     示例一：創(chuàng)建一個(gè)DDL觸發(fā)器審核數(shù)據(jù)庫(kù)級(jí)事件

復(fù)制代碼 代碼如下:

/***************
創(chuàng)建一個(gè)審核表,其中EventData是一個(gè)XML數(shù)據(jù)列
3w@live.cn
*******************/

USE master
GO
CREATE TABLE dbo.ChangeAttempt
(EventData xml NOT NULL,
AttemptDate datetime NOT NULL DEFAULT GETDATE(),
DBUser char(50) NOT NULL)
GO

/***************
在目標(biāo)數(shù)據(jù)庫(kù)上創(chuàng)建一個(gè)觸發(fā)器,以記錄該數(shù)據(jù)庫(kù)的索引變化動(dòng)作，
包括Create|alter|Drop
3w@live.cn
*******************/

CREATE TRIGGER db_trg_RestrictINDEXChanges
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
SET NOCOUNT ON
INSERT dbo.ChangeAttempt
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
GO

/***************
創(chuàng)建一個(gè)索引，以測(cè)試觸發(fā)器
3w@live.cn
*******************/

CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON
dbo.ChangeAttempt(DBUser)
GO

/***************
查看審核記錄
3w@live.cn
*******************/

SELECT EventData
FROM dbo.ChangeAttempt



--------/***************
--------刪除測(cè)試觸發(fā)器和記錄表
--------3w@live.cn
--------*******************/

----drop TRIGGER [db_trg_RestrictINDEXChanges]
----ON DATABASE
----go
----drop table dbo.ChangeAttempt
----go

執(zhí)行結(jié)果：

ROLLBACK
GO

--------/***************
--------試圖創(chuàng)建一個(gè)登錄賬號(hào)
--------3w@live.cn
--------*******************/
CREATE LOGIN johny WITH PASSWORD = '123456'
GO

--------/***************
--------刪除演示觸發(fā)器
--------3w@live.cn
--------*******************/

drop TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
go

效果：

GO

--------/***************
--------演示數(shù)據(jù)庫(kù)和審核表
--------3w@live.cn
--------*******************/

CREATE DATABASE ExampleAuditDB
GO
USE ExampleAuditDB
GO

CREATE TABLE dbo.RestrictedLogonAttempt
(LoginNM sysname NOT NULL,
AttemptDT datetime NOT NULL)
GO

--------/***************
--------創(chuàng)建登錄觸發(fā)器,如果不是在7:00-17:00登錄，則記錄審核日志，并提示失敗
--------3w@live.cn
--------*******************/

USE master
GO
Create TRIGGER trg_logon_attempt
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()='nightworker' AND
DATEPART(hh,GETDATE()) BETWEEN 7 AND 17
BEGIN
ROLLBACK
INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
(LoginNM, AttemptDT)
VALUES (ORIGINAL_LOGIN(), GETDATE())
END
END
GO

--------/***************
--------查看審核記錄
--------3w@live.cn
--------*******************/
USE ExampleAuditDB
GO
select * from dbo.RestrictedLogonAttempt
go

--------/***************
--------刪除演示數(shù)據(jù)庫(kù)及演示觸發(fā)器
--------3w@live.cn
--------*******************/
use master
go

drop TRIGGER trg_logon_attempt
ON ALL SERVER
go

drop database ExampleAuditDB
go

結(jié)果：

當(dāng)然，你也可以使用應(yīng)用程序或類似于log4net的日志機(jī)制記錄類似的登錄事件，但SQL server 2008已經(jīng)為我們做到了，你所做的僅僅是有勇氣來試一試。

小結(jié)：作為對(duì)數(shù)據(jù)DDL操作和登錄事件的審核和監(jiān)控，SQL Server提供了比較完善的事件處理機(jī)制。這也是SQL server安全機(jī)制的一部分。下文將涉及SQL server數(shù)據(jù)庫(kù)級(jí)的透明加密,敬請(qǐng)關(guān)注。