SQL Server 2005 Beta 2 Transact-SQL 增强功能(三)

如果您在创建了该触发器的数据库中发出 CREATE TABLE 语句，则应当获得以下输出：

CREATE TABLE T1(col1 INT)
CREATE TABLE Issued
<EVENT_INSTANCE>
<PostTime>2003-04-17T13:55:47.093</PostTime>
<SPID>53</SPID>
<EventType>CREATE_TABLE</EventType>
<Database>testdb</Database>
<Schema>dbo</Schema>
<Object>T1</Object>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE T1(col1 INT)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

.Net SqlClient Data Provider: Msg 50000, Level 16, State 1, Procedure trg_capture_create_table, Line 10
New tables cannot be created in this database.
.Net SqlClient Data Provider: Msg 3609, Level 16, State 1, Line 1
Transaction ended in trigger. Batch has been aborted.

请注意，本文对 XML 输出进行了手动格式化，以便提高可读性。当您运行该代码时，会获得未经格式化的 XML 输出。

要删除该触发器，请发出以下语句：

DROP TRIGGER trg_capture_create_table ON DATABASE

DDL 触发器特别有用的方案包括 DDL 更改的完整性检查、审核方案以及其他方案。作为 DDL 完整性增强的示例，以下数据库级别触发器拒绝了创建不带主键的表的企图：

CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR CREATE_TABLE
AS
DECLARE @eventdata AS XML, @objectname AS NVARCHAR(257),
@msg AS NVARCHAR(500)

SET @eventdata = eventdata()
SET @objectname =
N''['' + CAST(@eventdata.query(''data(//SchemaName)'') AS SYSNAME)
+ N''].['' +
CAST(@eventdata.query(''data(//ObjectName)'') AS SYSNAME) + N'']''
IF OBJECTPROPERTY(OBJECT_ID(@objectname), ''TableHasPrimaryKey'') = 0
BEGIN
SET @msg = N''Table '' + @objectname + '' does not contain a primary key.''
+ CHAR(10) + N''Table creation rolled back.''
RAISERROR(@msg, 16, 1)
ROLLBACK
RETURN
END

当 CREATE TABLE 语句发出时，该触发器被激发。该触发器使用 XQuery 提取架构和对象名称，并且使用 OBJECTPROPERTY 函数检查该表是否包含主键。如果不包含，则该触发器会生成错误并回滚事务。在创建该触发器之后，以下创建不带主键的表的尝试将失败：

CREATE TABLE T1(col1 INT NOT NULL)
Msg 50000, Level 16, State 1, Procedure trg_create_table_with_pk, Line 19
Table [dbo].[T1] does not contain a primary key.
Table creation rolled back.
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

而以下尝试将成功：

CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)

要删除该触发器和表 T1，请运行以下代码：

DROP TRIGGER trg_create_table_with_pk ON DATABASE
DROP TABLE T1

作为审核触发器的示例，以下数据库级别触发器针对 AuditDDLEvents 表审核所有 DDL 语句：

CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,

loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audi