DDL触发器.doc

只要注意到DDL触发器和DML触发器的区别，设计DDL触发器与设计DML触发器也很类似，下面详细讲述一下要怎么去设计一个DDL触发器。建立DDL触发器的语句建立DDL触发器的语法代码如下：CREATE TRIGGER trigger_nameON ALL SERVER | DATABASE WITH ,.n FOR | AFTER event_type | event_group ,.n AS sql_statement ; .n | EXTERNAL NAME ; 用中文取代一下英文可以看得更明白：CREATE TRIGGER 触发器名ON ALL SERVER或DATABASEFOR 或AFTER激活DDL触发器的事件请在此输入内容. AS 要执行的SQL语句其中：l ON后面的All Server是将DDL触发器作用到整个当前的服务器上。如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。l ON后面的Database是将DDL触发器作用到当前数据库，只能在这个数据库上激活该触发器。l For或After是同一个意思，指定的是After触发器，DDL触发器不能指定的Stead Of触发器。l 激活DDL触发器的事件包括两种，在DDL触发器作用在当前数据库情况下可以使用以下事件：CREATE_APPLICATION_ROLEALTER_APPLICATION_ROLEDROP_APPLICATION_ROLECREATE_ASSEMBLYALTER_ASSEMBLYDROP_ASSEMBLYALTER_AUTHORIZATION_DATABASECREATE_CERTIFICATEALTER_CERTIFICATEDROP_CERTIFICATECREATE_CONTRACTDROP_CONTRACTGRANT_DATABASEDENY_DATABASEREVOKE_DATABASECREATE_EVENT_NOTIFICATIONDROP_EVENT_NOTIFICATIONCREATE_FUNCTIONALTER_FUNCTIONDROP_FUNCTIONCREATE_INDEXALTER_INDEXDROP_INDEXCREATE_MESSAGE_TYPEALTER_MESSAGE_TYPEDROP_MESSAGE_TYPECREATE_PARTITION_FUNCTIONALTER_PARTITION_FUNCTIONDROP_PARTITION_FUNCTIONCREATE_PARTITION_SCHEMEALTER_PARTITION_SCHEMEDROP_PARTITION_SCHEMECREATE_PROCEDUREALTER_PROCEDUREDROP_PROCEDURECREATE_QUEUEALTER_QUEUEDROP_QUEUECREATE_REMOTE_SERVICE_BINDINGALTER_REMOTE_SERVICE_BINDINGDROP_REMOTE_SERVICE_BINDINGCREATE_ROLEALTER_ROLEDROP_ROLECREATE_ROUTEALTER_ROUTEDROP_ROUTECREATE_SCHEMAALTER_SCHEMADROP_SCHEMACREATE_SERVICEALTER_SERVICEDROP_SERVICECREATE_STATISTICSDROP_STATISTICSUPDATE_STATISTICSCREATE_SYNONYMDROP_SYNONYMCREATE_TABLEALTER_TABLEDROP_TABLECREATE_TRIGGERALTER_TRIGGERDROP_TRIGGERCREATE_TYPEDROP_TYPECREATE_USERALTER_USERDROP_USERCREATE_VIEWALTER_VIEWDROP_VIEWCREATE_XML_SCHEMA_COLLECTIONALTER_XML_SCHEMA_COLLECTIONDROP_XML_SCHEMA_COLLECTION在DDL触发器作用在当前服务器情况下，可以使用以下事件：ALTER_AUTHORIZATION_SERVERCREATE_DATABASEALTER_DATABASEDROP_DATABASECREATE_ENDPOINTDROP_ENDPOINTCREATE_LOGINALTER_LOGINDROP_LOGINGRANT_SERVERDENY_SERVERREVOKE_SERVER例三，建立一个DDL触发器，用于保护数据库中的数据表不被修改，不被删除。具体操作步骤如下：（1）启动Management Studio，登录到指定的服务器上。（2）在如图11.1所示界面的【对象资源管理器】下选择【数据库】，定位到【Northwind】数据库上。（3）单击【新建查询】按钮，在弹出的【查询编辑器】的编辑区里输入以下代码：CREATE TRIGGER 禁止对数据表操作ON DATABASEFOR DROP_TABLE, ALTER_TABLEAS PRINT 对不起，您不能对数据表进行操作 ROLLBACK ;（4）单击【执行】按钮，生成触发器。例四，建立一个DDL触发器，用于保护当前SQL Server服务器里所有数据库不能被删除。具体代码如下：CREATE TRIGGER 不允许删除数据库ON all serverFOR DROP_DATABASEAS PRINT 对不起，您不能删除数据库 ROLLBACK ;GO例五，建立一个DDL触发器，用来记录数据库修改状态。具体操作步骤如下：（1）建立一个用于记录数据库修改状态的表：CREATE TABLE 日志记录表( 编号 int IDENTITY(1,1) NOT NULL, 事件 varchar(5000) NULL, 所用语句 varchar(5000) NULL, 操作者 varchar(50) NULL, 发生时间 datetime NULL,CONSTRAINT PK_日志记录表 PRIMARY KEY CLUSTERED( 编号 ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY) ON PRIMARYGO（2）建立DDL触发器：CREATE TRIGGER 记录日志ON DATABASEFOR DDL_DATABASE_LEVEL_EVENTSASDECLARE log XMLSET log = EVENTDATA()INSERT 日志记录表 (事件, 所用语句,操作者, 发生时间) VALUES ( log.value(/EVENT_INSTANCE/EventType)1, nvarchar(100), log.value(/EVENT_INSTANCE/TSQLCommand)1, nvarchar(2000), CONVERT(nvarchar(100), CURRENT_USER), GETDATE() ) ;GO其中Eventdata是个数据库函数，它的作用是以XML格式返回有关服务器或数据库事件的信息。log.value是返回log这个XML结点的值，结点的位置是括号里的第一个参数。11.15.2 测试触发器功能现在测试一下在上一章节中建立好的三个触发器的功能。下面所有的测试都是在【查询编辑器】对话框里进行的，要打开【查询编辑器】对话框，只要单击Management Studio里【新建查询】按钮即可。测试例三：例三是保证【Northwind】数据库里不能删除表和修改表，在【查询编辑器】对话框里输入一个删除表的SQL语句：Drop table 操作记录表运行结果如图11.17所示：请在此输入内容. 请在此输入内容. 图11.17 不允许删除表格测试例四：例四是保证当前服务器里的所有数据库不能被删除，在【查询编辑器】对话框里输入一个删除数据库的SQL语句：Drop DataBase test运行结果如图11.18所示：请在此输入内容. 图11.18 不允许删除数据库测试例五：例五是记录对【Northwind】所进行的操作，在【查询编辑器】对话框里输入一条添加数据表和一条删除数据表的SQL语句，然后再用Select语句查看【目志记录表】数据表里所有的记录：CREATE TABLE 测试表( 编号int IDENTITY(1,1) NOT NULL, 测试内容varchar(50) NOT NULL)GODrop table 测试表GOselect * from 日志记录表GO运行时不要忘了，前面曾经建立过一个不能删除数据表的触发器，要先把它禁用或删除。运行结果如图11.19所示：请在此输入内容. 图11.19 记录对数据库的操作DDL触发器有两种，一种是作用在当前SQL Server服务器上的，一种是作用在当前数据库中的。这两种DDL触发器在Management Studio中所在的位置是不同的。l 作用在当前SQL Server服务器上的DDL触发器所在位置是：【对象资源管理器】，选择所在SQL Server服务器，定位到【服务器对象】【触发器】，在【摘要】对话框里就可以看到所有的作用在当前SQL Server服务器上的DDL触发器。l 作用在当前数据库中的DDL触发器所在位置是：【对象资源管理器】，选择所在SQL Server服务器，【数据库】，所在数据库，定位到【可编程性】【数据库触发器】，在摘要对话框里就可以看到所有的当前数据库中的DDL触发器。右击触发器，在弹出的快捷菜单中选择【编写数据库触发器脚本为】【CREATE到】【新查询编辑器对话框】，然后在新打开的【查询编辑器】对话框里可以看到该触发器的内容。在Management Studio如果要修改DDL触发器内容，就只能先删除该触发器，再重新建立一个DDL触发器。虽然在Management Studio中没有直接提供修改DDL触发器的对话框，但在【查询编辑器】对话框里依然可以用SQL语句来进行修改。下面给出几个对DDL触发器操作常用的SQL代码，由于对DDL触发器的操作和对DML触发器的操作类似，因此不再详细说明用法。l 创建DDL触发器CREATE TRIGGER (Transact-SQL)