47. SQL- 触发器之DDL 触发器

触发器工作原理

DDL触发器

1. 与DML 触发器不同，DDL 触发器不会为响应针对表或视图的UPDATE、INSERT或DELETE 语句而激发。

2. 响应多种数据定义语言(DDL) 语句而激发。主要是以CREATE、ALTER和DROP 开头的语句。

3. DDL 触发器可用于管理任务，例如审核和控制数据库操作。

4. DDL 触发器的作用域：

l 数据库作用域

l 服务器作用域

在响应当前数据库或服务器中处理的Transact-SQL 事件时，将激发DDL 触发器。触发器的作用域取决于事件(数据库作用域事件和服务器作用域事件)。每个事件都对应一个Transact-SQL 语句，语句语法经过修改，在关键字之间包含了下划线(―_‖)。

1. DDL 触发器作用：

• 防止对数据库架构进行某些更改。

• 数据库中发生某种情况以响应数据库架构中的更改。

• 记录数据库架构中的更改或事件。

• 仅在运行触发DDL触发器的DDL 语句后，DDL 触发器才会激发。

• DDL 触发器无法作为INSTEADOF 触发器使用。

DEMO1：

CREATETRIGGER safety

ONDATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT‘You must disable Trigger "safety" to drop or altertables!‘

ROLLBACK

DEMO2：

CREATETRIGGER ddl_trig_login

ON ALL SERVER FOR DDL_LOGIN_EVENTS

AS

PRINT‘Login Event Issued.‘

SELECT

EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,‘nvarchar(max)‘)

GO

DROPTRIGGER ddl_trig_login

ON ALL SERVER

GO

DDL触发器

1. DDL 触发器是SQL Server2005 新增的一个触发器类型，是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发。一般用于数据库中执行管理任务。

2. 与DML触发器一样，DDL 触发器也是通过事件来激活，并执行其中 的SQL 语句的。

3. 但与DML触发器不同，DML 触发器是响应Insert、Update 或Delete语句而激活的，DDL触发器是响应Create、 Alter 或Drop开头的语句而激活的。

4. 一般来说，在以下几种情况下可以使用DDL触发器：

数据库里的库架构或数据表架构很重要，不允许被修改。

防止数据库或数据表被误操作删除。

在修改某个数据表结构的同时修改另一个数据表的相应的结构。

要记录对数据库结构操作的事件。

创建DDL触发器

语法：

CREATETRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER} { event_type | event_group } [ ,...n ]

AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < methodspecifier > [; ] }

翻译成中文：

CREATETRIGGER 触发器名

ON ALL SERVER或DATABASE

FOR 或AFTER 激活DDL触发器的事件

AS 要执行的SQL语句

参数：

ON 后面的AllServer 是将DDL 触发器作用到整个当前的服务器上。如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。

ON 后面的Database是将DDL 触发器作用到当前数据库，只能在这个数据库上激活该触发器。

For 或After是同一个意思，指定的是After 触发器，DDL 触发器不能指定的Stead Of 触发器。

激活DDL 触发器的事件包括两种，

在DDL 触发器作用在当前数据库情况下可以使用以下事件：

CREATE_APPLICATION_ROLEALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE

CREATE_ASSEMBLYALTER_ASSEMBLY DROP_ASSEMBLY

ALTER_AUTHORIZATION_DATABASE

CREATE_CERTIFICATEALTER_CERTIFICATE DROP_CERTIFICATE

CREATE_CONTRACTDROP_CONTRACT

GRANT_DATABASEDENY_DATABASE REVOKE_DATABASE

CREATE_EVENT_NOTIFICATIONDROP_EVENT_NOTIFICATION

CREATE_FUNCTIONALTER_FUNCTION DROP_FUNCTION

CREATE_INDEXALTER_INDEX DROP_INDEX

CREATE_MESSAGE_TYPEALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE

CREATE_PARTITION_FUNCTIONALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION

CREATE_PARTITION_SCHEMEALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME

CREATE_PROCEDUREALTER_PROCEDURE DROP_PROCEDURE

CREATE_QUEUEALTER_QUEUE DROP_QUEUE

CREATE_REMOTE_SERVICE_BINDINGALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING

CREATE_ROLEALTER_ROLE DROP_ROLE

CREATE_ROUTEALTER_ROUTE DROP_ROUTE

CREATE_SCHEMAALTER_SCHEMA DROP_SCHEMA

CREATE_SERVICEALTER_SERVICE DROP_SERVICE

CREATE_STATISTICSDROP_STATISTICS UPDATE_STATISTICS

CREATE_SYNONYMDROP_SYNONYM CREATE_TABLE

ALTER_TABLEDROP_TABLE

CREATE_TRIGGERALTER_TRIGGER DROP_TRIGGER

CREATE_TYPEDROP_TYPE

CREATE_USERALTER_USER DROP_USER

CREATE_VIEWALTER_VIEW DROP_VIEW

CREATE_XML_SCHEMA_COLLECTIONALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

在DDL 触发器作用在当前服务器情况下，可以使用以下事件：

ALTER_AUTHORIZATION_SERVER

CREATE_DATABASEALTER_DATABASE DROP_DATABASE

CREATE_ENDPOINTDROP_ENDPOINT

CREATE_LOGINALTER_LOGIN DROP_LOGIN

GRANT_SERVERDENY_SERVER REVOKE_SERVER

DEMO1：建立一个DDL 触发器，用于保护数据库中的数据表不被修改，不被删除

CREATETRIGGER deny_drop_table

ONDATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT‘sorry，You can not operate on the data table‘

ROLLBACK

DEMO2：建立一个DDL 触发器，用于保护当前SQL Server 服务器里所有数据库不能被删除。

CREATETRIGGER deny_drop_db

ON all server

FOR DROP_DATABASE

AS

PRINT‘sorry，You can not drop a database‘

ROLLBACK;

GO

DEMO3：创建一个数据库操作记录表，用于记录当数据库发生操作时的操作事件

--1、创建测试表

CREATETABLE logRecord_table

(

seq_numint IDENTITY(1,1) NOT NULL, --编号

event_typevarchar(5000) NULL, --事件

Sentencevarchar(5000) NULL, --所用语句

oper_uservarchar(50) NULL, --操作者

event_timedatetime NULL, --发生时间

CONSTRAINTPK_logRecord_table PRIMARY KEYCLUSTERED

( seq_num ASC )

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

GO

--2、建立DDL 触发器

CREATETRIGGER logRecord

ONDATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE@log XML

SET @log = EVENTDATA()

INSERTlogRecord_table

(event_type, Sentence,oper_user, event_time)

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 结点的值，结点的位置是括号里的第一个参数。

--3、根据之前创建的触发器测试

droptable tb1 --删除表tb1

go

dropdatabase db1 --删除数据库db1

go

返回：

-- 创建一个测试表，然后删除这个测试表，查看日志记录表情况，需先删除或禁用deny_drop_table触发器

CREATETABLE 测试表(

编号int IDENTITY(1,1) NOT NULL,

测试内容varchar(50) NOTNULL)

Droptable 测试表

GO

select* from logRecord_table

GO

返回：

管理DDL触发器：

--创建DDL触发器

CREATETRIGGER (Transact-SQL)

--删除DDL触发器

DROPTRIGGER (Transact-SQL)

--修改DDL触发器

ALTERTRIGGER (Transact-SQL)

--重命名DDL触发器

sp_rename(Transact-SQL)

--禁用DDL触发器

DISABLETRIGGER (Transact-SQL)

--启用DDL触发器

ENABLETRIGGER (Transact-SQL)

--删除DDL触发器

DROPTRIGGER (Transact-SQL)

本文出自 “Ricky's Blog” 博客，请务必保留此出处http://57388.blog.51cto.com/47388/1630407