SQL Server 2005 包括对 Transact-SQL (T-SQL) 语言的几项重要改进。新增功能之一是一种新的触发器,它在数据定义语言 (DDL) 语句运行时激发。
TRY/CATCH 结构捕获严重度为 11 到 19(包括 11 和 19)的错误。低于 11 的严重级别是警告,不视为错误。严重级别为 20 及以上的错误视为严重错误。然而,如果这些严重级别为 20 及以上的错误没有导致数据库引擎停止,则它们将由 TRY/CATCH 捕获。总之,TRY/CATCH 语句更易于读取、更易于维护,且更不容易导致复制和粘贴错误。此外,@@ERROR 技术也不总是很可靠。例如,某些错误会中止计划或整个批处理。
问:我知道可以创建触发器来审核对数据的更改,但是如果表的架构发生更改,我如何执行操作呢?
答:我在简介中提到过,现在有了一个新的触发器:SQL Server 2005 中的 DDL 触发器。数据操作语言 (DML) 触发器基于数据操作激发,而 DDL 触发器在对数据库架构或服务器进行更改时激发。创建的 DDL 触发器只能在发生触发事件之后激发,而 DML 触发器则不同,它可以在事件之后或代替事件激发。以下是创建 DDL 触发器的语法,在 SQL Server 2005 文档中进行了概述:
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
{ AS
{ sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }
}
< method_specifier > ::= assembly_name:class_name[::method_name] |
请注意,您可以创建触发器,以便它在对数据库的架构或数据库服务器进行更改时激发。您还可以通过指定事件类型(这些类型都列在 SQL Server 2005 文档中)来定义导致触发器激发的事件。或者,也可以通过指定 DDL_DATABASE_LEVEL_EVENTS 标识符让 DDL 触发器在所有事件之后激发。
通过 DDL 触发器,您可以记录对数据库的架构所作的更改。可以使用 DDL 触发器审核更改,而不是防止更改。或者,也可以通过 DDL 触发器记录尝试操作,然后将其回滚。您可能还希望在创建对象时对其强制命名约定。例如,您可能希望所有存储过程都以 pr 为前缀。通过 DDL 触发器,您可以强制该命名约定。
DDL 触发器与 DML 触发器的类似之处在于,它们都可以回滚事务。然而,DDL 触发器没有插入或删除的表。DDL 触发器能够访问一个名为 eventdata 的内置函数,该函数在 XML 数据类型中返回 XML(包含有关激发触发器的事件的信息)。每个事件在事件数据中显示的信息都略有不同。但是,eventdata 函数始终为所有事件返回以下信息:
• 事件的时间
• 事件的类型
• 导致触发器激发的连接 SPID
• 发生事件的用户上下文的登录名和用户名
CREATE TRIGGER ddlTrigger_ProcNamingConvention
ON DATABASE
AFTER CREATE_PROCEDURE, ALTER_PROCEDURE
AS
DECLARE @eventDataXml XML
SET @eventDataXml = EVENTDATA()
IF SUBSTRING(@eventDataXml.value('(//ObjectName)[1]',
'VARCHAR(200)'), 1, 2) = 'pr'
PRINT 'starts with pr'
ELSE
BEGIN
PRINT 'does not start with pr'
ROLLBACK TRANSACTION
END
GO |
请注意,这段代码中的 DDL 触发器定义为,在当前数据库上创建或更改存储过程时激发。当触发器激发后,事件的数据会被捕获并存储在本地 XML 变量中。尽管这不是必要的,但如果您打算多次访问 eventdata 函数,这有助于使触发器更高效。然后,触发器会检查对象的名称是否以 pr 开头。如果是,则允许事务完成;如果不是,则回滚事务,并且存储过程将保持不变。在本例中,我知道 ObjectName 元素将包含存储过程的名称,因为仅在创建或更改存储过程时才会调用触发器。
<EVENT_INSTANCE>
<EventType>CREATE_PROCEDURE</EventType>
<PostTime>2005-10-20T00:52:16.160</PostTime>
<SPID>51</SPID>
<ServerName>MYDBSERVER</ServerName>
<LoginName>CAMELOT\jpapa</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>test1</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>create proc test1 as select getdate()</CommandText>
</TSQLCommand>
</EVENT_INSTANCE> |
显示执行以下命令后该触发器的事件数据:
create proc test1 as select getdate |
您还可以捕获事件数据,并将其完整存储到审核表的 XML 列中。另一个选择是抽出特定节点的内容,并将其单独存储。显然,实现视具体情况而定,但 DDL 触发器为您提供的选择有很多。使用 DDL 触发器时需要记住的一件事是,它们是同步操作的。由于是同步执行,触发器应该尽可能不执行费时的操作。否则,它们会对数据库服务器的性能造成负面影响。与 DML 触发器一样,应该将 DDL 触发器定义为仅执行所需的操作并快速完成。如果需要异步执行,可以使用 Service Broker 和 Event Notification 模型。