SQL2005 DDL触发器
在数据库中有三种数据语言:
DDL(Data Definition Language):数据定义语言。如Create Drop Alter等
DML(Data Manipulation):数据操纵语言。如Insert ,Update,Delete
DCL(Data Control Language):数据控制语言 .如Grant,Revoke等
在2000中只可以为DML做触发器,而在2005中,可以为DDL做触发器,我们叫它DDL触发器
一:
Microsoft SQL Server 2005 在数据库引擎 中引入了多项能提高程序员的开发能力和工作效率的改进和新功能。其中DDL触发器就是其中之一。
除了对修改数据的数据操作语言语句(INSERT、UPDATE 和 DELETE)所激发的触发器提供支持外,数据库引擎 还包括一类新的触发器,由修改数据库对象的 DDL 语句(如以 CREATE、ALTER 或 DROP 开头的语句)激发。DDL 触发器可用于审核或控制对数据库架构的更改。
像常规触发器一样,DDL 触发器将激发存储过程以响应事件,这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务,例如审核和控制数据库操作。
仅在运行触发 DDL 触发器的 DDL 语句后,DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。
二:
在响应当前数据库或服务器中处理的 Transact-SQL 事件时,可以激发 DDL 触发器。触发器的作用域取决于事件。例如,每当数据库中发生 CREATE TABLE 事件时,都会触发为响应 CREATE TABLE 事件创建的 DDL 触发器。每当服务器中发生 CREATE LOGIN 事件时,都会触发为响应 CREATE LOGIN 事件创建的 DDL 触发器。
在下面的示例中,每当数据库中发生 DROP TABLE 事件或 ALTER TABLE 事件,都将触发 DDL 触发器 safety:
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK
;
数据库范围内的 DDL 触发器都作为对象存储在创建它们的数据库中。可以在 master 数据库中创建 DDL 触发器,这些 DDL 触发器的行为与在用户设计的数据库中创建的 DDL 触发器一样。可以从创建 DDL 触发器的数据库上下文中的 sys.triggers 目录视图中,或通过指定数据库名称作为标识符(例如 master.sys.triggers)来获取有关这些 DDL 触发器的信息。
服务器范围内的 DDL 触发器作为对象存储在 master 数据库中。不同的是,可以从任何数据库上下文中的 sys.server_triggers 目录视图中获取有关数据库范围内的 DDL 触发器的信息。
使用 EVENTDATA 函数,可以捕获有关激发 DDL 触发器的事件的信息。此函数返回 xml 值。
三:
以下示例创建用于存储所有数据库级事件的相关信息的表,并在表中填充 DDL 触发器。通过对 EVENTDATA 生成的 XML 数据使用 XQuery,可以捕获事件类型和 Transact-SQL 语句。
USE AdventureWorks;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--Test the trigger.
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
GO
--Drop the trigger.
DROP TRIGGER log
ON DATABASE
GO
--Drop table ddl_log.
DROP TABLE ddl_log
GO