SQL Server DDL 触发器(Trigger)-- 创建服务器级别的DDL触发器

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

若是创建服务器级别的DDL触发器,只要把先前的ON DATABASE改为ON ALL SERVER,即可跟踪服务器级别的事件,使用的原理与数据库级别的DDL触发器相似,区别只在跟踪的事件不同。

 

1
2
3
4
5
6
CREATE  TRIGGER  ddl_trig_login
ON  ALL  SERVER
FOR  DDL_LOGIN_EVENTS
AS
PRINT n’ ALTER  LOGIN EVENT’
SELECT  EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar( max )’)

 

跟踪DDL_LOGIN_EVENTS类型事件,只要有新建、修改、删除登录帐号的事件发生,触发器就会执行程序,同样也可以利用EVENTDATA()函数取得触发器执行时相关的系统信息。可以通过如下语句来测试:

 

1
CREATE  LOGIN test  WITH  PASSWORD =’Mpdfzh7’

 

同样地,如果要删除服务器级别的DDL触发器,和数据库级别的DDL触发器差不多,只是改成ON ALL SERVER即可,请参考如下语句:

 

1
2
DROP  TRIGGER  ddl_trig_login
ON  ALL  SERVER

 

当要禁止某个人或某个应用程序登录SQL Server时,也可以通过LOGON事件所引发的触发器。例如,SQL Server 2008后,Management Studio所提供的T-SQL IntelliSense功能会占据少许服务器的CPU和内存资源。若服务器端的数据对象不多,影响较小;但若对象很多,同时联机编辑的用户也很多,则会耗掉较大的资源。你可以通过LOGON事件所引发的触发器。不准SQL Server Management Studio工具程序用来查询T-SQL IntelliSense所需数据的连接登录。

 

首先,通过SQL Server Profiler工具程序观察,当Management Studio的T-SQL编辑器要取得服务器端数据对象,以提供开发程序T-SQL IntelliSense时,会用什么样的应用程序名称登录SQL Server,录制结果如下。(备注:作者使用SQL Server 2012录制,没有捕获到该应用的相关事件,下面测试在SQL Server 2008 R2下完成,SQL Server 2008 R2 Management Studio的T-SQL编辑环境会先查询服务器端的对象后,再提供T-SQL编写时的IntelliSense功能)

 

clip_image001

 

针对LOGON事件,创建简单服务器级别的DDL触发器后,通过APP_NAME系统函数判断登录的应用程序名称,若类似“Microsoft SQL Server Management Studio - Transact-SQL IntelliSense”,便通过ROLLBACK命令使其无法连接,代码如下。

 

1
2
3
4
5
6
7
CREATE  TRIGGER  IntelliSense_Connection_Limit_Trigger
ON  ALL  SERVER  FOR  LOGON
AS
BEGIN
IF APP_NAME()  LIKE  ‘% Microsoft SQL Server Management Studio - Transact-SQL IntelliSense %’
ROLLBACK ;
END ;

 

执行之后,SQL Server 2008 R2 Management Studio的T-SQL编辑器所提供的IntelliSense功能就失效了。

 

利用相同的技巧,也可以写一个DDL触发器,当服务器连接过多时,就不准再创建新的、不重要的连接,以维持数据库的稳定和效率,并让既有的连接得以完成工作。免得系统在极为忙碌时,再加一条连接,有如最后一根稻草,压垮已经运行一阵的其他业务。不过,若真这么做,你可能要为了服务器的管理而跟开发人员吵架了。

 

另外,当设置SQL Server 2008 R2所提供的“Policies”机制,其“Evaluation Mode”为“on change -- prevent”时,SQL Server也是利用服务器几倍的DDL触发器,在事件发生后,立即评估其操作内容是否符合先前指定的策略,若违反策略,便ROLLBACK回滚原始状况。

 

clip_image002

 

clip_image003

 

在设置“Policies”时,需要“Evaluation Mode”为“on change -- prevent”,且“Enabled”,才会自动创建服务器级别的DDL触发器。若实际观察该触发器,可以看到如下内容。

 

1
2
3
4
5
6
7
8
9
CREATE  TRIGGER  [syspolicy_server_trigger]  ON  ALL  SERVER
WITH  EXECUTE  AS  '##MS_PolicyEventProcessingLogin##'
FOR  ALTER_AUTHORIZATION_DATABASE,ALTER_PROCEDURE,ALTER_SCHEMA,CREATE_PROCEDURE,RENAME
AS
BEGIN
DECLARE  @event_data xml
SELECT  @event_data = EVENTDATA()
EXEC  [msdb].[dbo].[sp_syspolicy_dispatch_event] @event_data = @event_data, @synchronous = 1
END















本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1598188  ,如需转载请自行联系原作者


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
数据库开发之SQL简介以及DDL的详细解析
数据库开发之SQL简介以及DDL的详细解析
41 0
|
10天前
|
SQL 流计算
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(4)
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】
|
10天前
|
SQL Java 流计算
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(3)
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】
|
10天前
|
SQL 消息中间件 存储
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(2)
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】
|
10天前
|
SQL 关系型数据库 API
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(1)
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(1)
|
10天前
|
SQL 存储 关系型数据库
MySQL数据库——SQL(1)-SQL通用语法、SQL分类、DDL(数据库操作、表操作)
MySQL数据库——SQL(1)-SQL通用语法、SQL分类、DDL(数据库操作、表操作)
18 1
|
12天前
|
存储 SQL 数据库
SQL 语言:存储过程和触发器
SQL 语言:存储过程和触发器
30 6
|
13天前
|
SQL 存储 关系型数据库
sql数据库服务器
SQL数据库服务器是由Microsoft所开发的数据库服务器,它可以让一个企业利用Internet/Intranet来整合所需的数据库。SQL Server最初是由Sybase提供的,但Microso
|
1月前
|
SQL Oracle 关系型数据库
数据库sqlserver-----触发器的插入,更新和删除
数据库sqlserver-----触发器的插入,更新和删除
39 3
|
1月前
|
存储 SQL
SQL Server 存储过程 触发器 事务处理
SQL Server 存储过程 触发器 事务处理

热门文章

最新文章