SQL中的索引和触发器

一、 索引

1.1 索引类型

聚集索引

表中各行的物理顺序与键值的逻辑顺序相同

1. 主键索引： 为定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型
2. 主键索引要求主键中的每一个值是唯一的，并且不能为空

非聚集索引

非聚集索引指定表的逻辑顺序

1. 数据存储在一个位置，索引储存在另一个位置，索引中包含指向数据存储位置的指针
2. 可以有多个，小于249个
3. 唯一索引：唯一索引不允许两个具有相同的索引值

1.2 创建索引

CREATE [UNIQUE][CLUSTERED][NONCLUSTERED] INDEX 索引名称 ON 表名称
1

FILLFACTOR填充因子：指定一个1-100之间的值，表示索引页填充的百分比

1.3 删除索引

DROP INDEX 表名称，索引名
1

删除表时，该表的所有索引同时会被删除

1.4 索引的优点

1. 创建唯一性索引，保证数据库表中每一行数据的唯一性。

2. 大大加快数据的检索速度，这也是创建索引的最主要原因。

3. 加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4. 在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。

   5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

1.5 索引的缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需 要的空间就会更大。

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，降低了数据的维护速度

二、触发器

2.1 触发器概述

触发器是一种特殊类型的存储过程，当指定表中的数据发生变化时触发器自动生效。它与表紧密相连，可以看做是表定义的 一部分。触发器不能通过名称被直接调用，更不允许设置参数。在SQL Server中一张表可以有多个触发器。用户可以使用INSERT、UPDATE或DELETE语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。触发器可以包含复杂的Transact-SQL语句。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。

2.2 触发器的优点

触发器的优点表现在以下几个方面：

1. 触发器自动执行，对表中的数据进行修改后，触发器立即被激活。

2. 为了实现复杂的数据库更新操作，触发器可以调用一个或多个存储过程，甚至可以通过调用外部过程（不是数据库管理 系统本身）完成相应的操作

3. 触发器能够实现比CHECK约束更为复杂的数据完整性约束。在数据库中，为了实现数据完整性约束，可以使用CHECK约 束或触发器。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。它更适合在大型数据库管理系统中用来约束数据的完整性

4. 触发器可以检测数据库内的操作，从而取消了数据库未经许可的更新操作，使数据库修改、更新操作更安全，数据库的 运行也更稳定

5. 触发器能够对数据库中的相关表实现级联更改。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据 库中相关表的级联更改

   6.一个表中可以同时存在3个不同操作的触发器（INSERT、UPDATE和DELETE）

2.3 触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。触发器的主要作用主要有以下接个方面：

1. 强制数据库间的引用完整性
2. 级联修改数据库中所有相关的表，自动触发其它与之相关的操作
3. 跟踪变化，撤销或回滚违法操作，防止非法修改数据
4. 返回自定义的错误消息，约束无法返回信息，而触发器可以
5. 触发器可以调用更多的存储过程

2.4 触发器的分类

SqlServer包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。

1.DML(数据操作语言,Data Manipulation Language)触发器

DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。SqlServer中的DML触发器有三种：

1. insert触发器:向表中插入数据时被触发；
2. delete触发器：从表中删除数据时被触发；
3. update触发器：修改表中数据时被触发。

当遇到下列情形时，应考虑使用DML触发器：

1. 通过数据库中的相关表实现级联更改
2. 防止恶意或者错误的insert、update和delete操作，并强制执行check约束定义的限制更为复杂的其他限制。
3. 评估数据修改前后表的状态，并根据该差异才去措施。

2.DDL(数据定义语言,Data Definition Language)触发器

DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

3.登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

2.5 触发器的工作原理

触发器触发时：

1. 系统自动在内存中创建deleted表或inserted表；
2. 只读，不允许修改，触发器执行完成后，自动删除。

inserted表：

1. 临时保存了插入或更新后的记录行；
2. 可以从inserted表中检查插入的数据是否满足业务需求；
3. 如果不满足，则向用户发送报告错误消息，并回滚插入操作。

deleted表：

1. 临时保存了删除或更新前的记录行；
2. 可以从deleted表中检查被删除的数据是否满足业务需求；
3. 如果不满足，则向用户报告错误消息，并回滚插入操作。

inserted表和deleted表对照：

2.6 创建DML触发器

如果用户要通过数据操作语言(DML)事件编辑数据，则执行DML触发器。DML事件是针对表或视图的INSERT、UPDATE 或DELETE语句

CREATE TRIGGER Cou_Trig
ON Course AFTER INSERT AS
INSERT INTO Teacher VALUES('08','zhangsan')
1
2
3

这个触发器的含义是当Course表在进行INSERT改变时，则自动激活该触发器，自动执行AS之后的语句块

CREATE TRIGGER Cou_TrigDele ON Course
AFTER DELETE AS
DELETE Teacher WHERE Tname='zhangsan'
1
2
3

1.DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。

SqlServer中的DML触发器有三种：

1. insert触发器：向表中插入数据时被触发；

2. delete触发器：从表中删除数据时被触发；

3. update触发器：修改表中数据时被触发。

2. DDL(数据定义语言 Language)触发器

DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使 用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

3. 登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器