SQL Server 2005 - 数据定义语言触发器(Data Definition Langua

SQL Server 2005得很多很增功能之一就是数据定义语言 (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()函数。

原文：