SQL Server 資料庫層級觸發器介紹

SQL Server 2005得很多很增功能之一就是資料定義語言 (Data Definition Language) (DDL) 觸發器。在SQL Server 7.0和2000中，我們使用資料操作語言 (DML) 觸發器，當INSERT,UPDATE或者DELETE語句被執行的時候執行一段SQL語句或預存程序，它們只能用於表或視圖。

　　在SQL Server 2005中，我們可以在DDL語句上建立一個觸發器來執行任何操作。觸發器的作用範圍可以是資料庫層或是伺服器層。DDL觸發器常用在資料庫結構要變更的時候你期望的某個動作被執行。

　　讓我們在資料庫上建立一個觸發器阻止任何使用者Create,Alter和Drop表或預存程序。我們假定我們有一個資料庫叫DDLTrTest，這個資料庫中有一個表(MyTable)和一個預存程序(Usp_QueryMyTable)。

　　用下面的語句建立資料庫，表和預存程序。
USE [MASTER]
GO
IF  EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME = N'DDLTRTEST')
DROP DATABASE [DDLTRTEST]
GO
CREATE DATABASE DDLTRTEST
GO
USE [DDLTRTEST]
GO
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[MYTABLE]') 
  AND TYPE IN (N'U'))
DROP TABLE [DBO].[MYTABLE]
GO
CREATE TABLE MYTABLE(ID INT, NAME VARCHAR(100))
GO
INSERT INTO MYTABLE SELECT 1,'A'
INSERT INTO MYTABLE SELECT 2,'B'
INSERT INTO MYTABLE SELECT 3,'C'
INSERT INTO MYTABLE SELECT 4,'D'
INSERT INTO MYTABLE SELECT 5,'E'
INSERT INTO MYTABLE SELECT 6,'F'
GO
USE [DDLTrTest]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
  OBJECT_ID(N'[dbo].[usp_querymytable]')
  AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_querymytable]
GO
CREATE PROC USP_QUERYMYTABLE
AS
SELECT * FROM MYTABLE
GO

　　我們再建立一個下面這樣的DDL觸發器STOP_DDL_on_Table_and_PROC。

CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON DATABASE 
FOR CREATE_TABLE,DROP_TABLE, 
  ALTER_TABLE,CREATE_PROCEDURE,
  ALTER_PROCEDURE,DROP_PROCEDURE
AS 
SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
  'nvarchar(max)')
PRINT 'You are not allowed to CREATE,ALTER and DROP 
  any Tables and Procedures' 
ROLLBACK;

　　這裡我們使用了一個SQL Server 2005提供的新函數EVENTDATA()來捕獲SQL語句。

　　現在我們嘗試用下面這個命令來給Mytable增加一個列。
Alter Table MyTable Add X INT

　　結果就會這樣：
Alter Table MyTable Add X INT
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

　　讓我們嘗試刪除表MyTable
Drop Table MyTable

　　結果將會這樣：
Drop Table MyTable
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

　　讓我們來嘗試建立一個預存程序
CREATE PROCEDURE USP_QUERYMYTABLE2 AS
SELECT TOP 100 * FROM MyTable

　　結果將會這樣
CREATE PROCEDURE USP_QUERYMYTABLE2 AS
SELECT TOP 100 * FROM MYTABLE
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Procedure USP_QUERYMYTABLE2, Line 3
The transaction ended in the trigger. The batch has been aborted.

STOP_DDL_on_Table_and_PROC這個DDL觸發器只是應用在資料庫DDLTrTest。在其他資料庫上Create,Alter和Drop都可以執行。

　　這些觸發器可以用下面的命令來停用，啟用或者刪除。
DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC 
ON DATABASE
GO
ENABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC 
ON DATABASE
GO
DROP TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON DATABASE
GO

　　如果你想把這個觸發器的作用範圍從資料庫層改變到伺服器層，你可以像下面這樣把ON DATABASE改成ON ALL SERVER。這個觸發器就阻止使用者

　　執行Create,Alter和Drop資料庫的操作。

CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
AS 
PRINT 'You are not allowed to CREATE,ALTER and DROP any Databases' 
ROLLBACK;

　　現在，我們再來試一下建立，修改和刪除一個資料庫。

USE MASTER
GO
CREATE DATABASE TEST
GO
ALTER DATABASE DDLTRTEST MODIFY FILE
(
NAME = DDLTRTEST,
FILENAME='D:\DDLTRTEST.MDF')
GO
USE MASTER
GO
DROP DATABASE DDLTRTEST
GO

　　結果如下：

You are not allowed to CREATE,ALTER and DROP any Databases
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
The file "DDLTRTEST" has been modified in the system catalog. 
  The new path will be used the next time the database is started.
You are not allowed to CREATE,ALTER and DROP any Databases
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
You are not allowed to CREATE,ALTER and DROP any Databases
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

　　伺服器層級的觸發器可以用下面的命令來停用，啟用或者刪除。

DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC 
ON ALL SERVER
GO
ENABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC 
ON ALL SERVER
GO
DROP TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON ALL SERVER
GO

總結
　　這篇文章主要探索了SQL Server 2005的新特性DDL觸發器和EVENTDATA()函數。

原文：http://kb.cnblogs.com/a/1140060/