数据库结构监控1 DML触发器介绍

最新推荐文章于 2024-10-14 22:42:36 发布

weixin_30293079

最新推荐文章于 2024-10-14 22:42:36 发布

阅读量115

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/hsj2010/archive/2010/10/19/1848585.html

版权

最近做了个数据库结构监控，所以简单介绍一下实现原理。

触发器介绍：

可以看帮助

也可以在博客园搜索一下其中一个：http://www.cnblogs.com/tdalcn/archive/2007/04/11/709744.html

我这里做个简单的介绍

触发器分类：

分为两大类：

DML：是当数据库服务器中发生数据操作语言 (DML) 事件时要执行的操作。DML 事件包括对表或视图发出的 UPDATE、INSERT 或 DELETE 语句。DML 触发器用于在数据被修改时强制执行业务规则，以及扩展 Microsoft SQL Server 约束、默认值和规则的完整性检查逻辑。

DDL：是为了响应各种数据定义语言 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

DDL 触发器是MSSQL2005才有的，2008对它做了改进。

当然还有什么CLR触发器。

以下介绍DML触发器：

DML：可以在SQL执行前触发（INSTEAD OF），也可以在执行后触发（AFTER）。不管是哪个它都是一个整体其中一个环节出错整个事务将回滚。

AFTER触发器：晚于：1。约束处理 2。声明性引用操作 3。创建插入的和删除的表

INSTEAD OF触发器：早于：约束处理替代：触发操作晚于：创建插入的和删除的表

INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

INSTEAD OF 触发器可以进行以下操作：

忽略批处理中的某些部分。

不处理批处理中的某些部分并记录有问题的行。

如果遇到错误情况则采取备用操作。

注意：对于含有使用 DELETE 或 UPDATE 级联操作定义的外键的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。

默认为： AFTER

可以有多个 AFTER 触发器，每个触发操作（UPDATE、DELETE 和 INSERT）只能有一个 INSTEAD OF 触发器。

多个触发器（AFTER），可以跟据触发操作（UPDATE、DELETE 和 INSERT）指定，首个触发哪个，最后触发哪个。

TRUNCATE TABLE 不会触发触发器。

DML触发器测试代码：

代码

1 create table demo_trig
2 (
3 id int ,
4 name varchar ( 50 )
5 )
6 go
7
8 create trigger trig_insertof
9 on demo_trig
10 instead of insert
11 as
12 begin
13      -- 不反回影响行
14      SET NOCOUNT ON
15
16      print ' trig_insertof '
17      -- 充许插入
18      insert into demo_trig select * from inserted
19 end
20 go
21
22 create trigger   trig_first
23 on demo_trig
24 for insert
25 as
26 begin
27      print ' trig_first '
28 end
29 go
30 create trigger   trig_last
31 on demo_trig
32 for insert
33 as
34 begin
35      print ' trig_last '
36 end
37 go
38 create trigger   trig_test1
39 on demo_trig
40 for insert
41 as
42 begin
43      print ' trig_test1 '
44 end
45 go
46 create trigger   trig_test2
47 on demo_trig
48 for insert
49 as
50 begin
51      print ' trig_test2 '
52 end
53 go
54 -- 设置首个执行的触发器
55 exec sp_settriggerorder @triggername = ' trig_first ' , @order = ' first ' , @stmttype = ' insert '
56 -- 设置最后一个
57 exec sp_settriggerorder @triggername = ' trig_last ' , @order = ' last ' , @stmttype = ' insert '
58
59
60 insert into demo_trig values ( 1 , ' 10 ' )
61
62 -- 执行结果(1 行受影响)
63
64 ( 1 行受影响)
65
66 ( 1 行受影响)
67
68 ( 1 行受影响)
69
70 ( 1 行受影响)
71
72 ( 1 行受影响)
73 trig_insertof
74 trig_first
75 trig_test1
76 trig_test2
77 trig_last
78
79 ( 1 行受影响)
80

触发器循环：

代码

1 create table demo_trig1
2 (
3 id int    identity ( 1 , 1 ),
4 name varchar ( 50 )
5 )
6 go
7 create table demo_trig2
8 (
9 id int identity ( 1 , 1 ),
10 name varchar ( 50 )
11 )
12 go
13
14 -- 插入 demo_trig1 同时插入 demo_trig2
15 create trigger   trig_demo_trig1_insert
16 on demo_trig1
17 for insert
18 as
19 begin
20      insert into demo_trig2 values ( ' trig_demo_trig1_insert ' )
21      print ' trig_demo_trig1_insert '
22 end
23 go
24
25 -- 插入  demo_trig2 同时插入 demo_trig1
26 create trigger   trig_demo_trig2_insert
27 on demo_trig2
28 for insert
29 as
30 begin
31      insert into demo_trig1 values ( ' trig_demo_trig2_insert ' )
32      print ' trig_demo_trig2_insert '
33 end
34 go
35 insert into demo_trig1 values ( ' 10 ' )
36 select * from   demo_trig1
37 select * from   demo_trig2
38
39 -- 执行结果
40 ( 1 行受影响)
41
42 ( 1 行受影响)
43
44 ( 1 行受影响)
45
46 ( 1 行受影响)
47 消息 217 ，级别 16 ，状态 1 ，过程 trig_demo_trig2_insert，第 8 行
48 超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32 )。
49
50
51

消息 217，级别 16，状态 1，过程 trig_demo_trig2_insert，第 8 行

超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。

递归之类的也是32层。只是插入数据失败，创建触发器是成功的。

触发器安全：

请查看帮助，帮助里面很详细。大致如下：

当一个没有XX权限的用户建立一个触发器（触发器代码将XX权限赋于XX用户），XX用户执行触发器将因为没有权限而失败。

当一个拥有XX权限的用户去执行触发器时，执行成功，此时XX权限赋于XX用户。入侵成功。

数据库结构监控1 DML触发器介绍

数据库结构监控2 DDL 触发器介绍

数据库结构监控3 代码分析1-监控数据库内部对象(表)创建删除修改

数据库结构监控3 代码分析2-监控创建数据库及系统数据库

数据库结构监控3 代码分析3-作业监控