SQL Server之触发器

触发器

一、概述

触发器是一种特殊类型的存储过程，主要是通过事件触发来执行的，而存储过程可以通过存储过程名来直接调用。当往某一个数据表中插入、修改或者删除记录时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器和引起触发器执行的SQL语句被当作一次事务处理，如果这次事务未获得成功，SQL Server会自动返回该事务执行前后状态。

触发器是一个在修改指定表值的数据时执行的存储过程，不同的是执行存储过程要使用EXEC语句来调用，而触发器的执行不需要使用EXEC语句来调用，通过创建触发器可以保证不同数据表中的相关数据的引用完整性或一致性。

触发器的优点：

1. 触发器是自动的。当对数据表中的数据做了任何修改（比如手工输入或者应用程序采取的操作）之后触发器会立即被激活。
2. 触发器可以通过数据库中的相关数据表进行层叠更改。
3. 触发器可以强制一些限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他数据表中的列（即数据表中的字段）。

1.1、触发器的作用

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

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

触发器与存储过程的主要区别在于触发器的运行方式，存储过程需要用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件（INSERT、UPDATE、DELETE）出现的时候，自动执行。

1.2、触发器的分类

1.2.1、数据操作语言触发器

数据操作语言（DML）触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。SQL Server中DML触发器有3种：INSERT触发器、UPDATE触发器和DELETE触发器。当遇到下面的情形时，考虑使用DML触发器：

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

在SQL Server中，针对每个DML触发器定义了两个特殊的表：DELETED表和INSERTED表，这两个逻辑表在内存中存放，由系统来创建和维护，用户不能对它们进行修改。触发器执行完成之后与该触发器相关的这两个表也会被删除。

1. DELETED表存放执行DELETE或者UPDATE语句时要从表中删除的行。在执行DELETE或UPDATE时，被删除的行从触发触发器的表中被移动到DELETED表，即DELETED表和触发触发器的表有公共的行。
2. INSERTED表存放执行INSERT或UPDATE语句时要向表中插入的行，在执行INSERT或UPDATE事务中，新行同时添加到触发触发器的表和INSERTED表。INSERTED表的内容是触发触发器的表中新行的副本，即INSERTED表中的行总是与触发触发器的表中的新行相同。

1.2.1、数据定义语言触发器

数据定义语言（DDL）触发器是当服务器或者数据库中发生数据定义语言事件时被激活而调用，使用DDL触发器可以防止对数据库架构进行的某些未授权更改。

二、创建DML触发器

DML触发器是指当数据库服务器中发生数据库操作语言事件时要执行的操作，DML事件包括对数据表或视图发生的INSERT、DELETE、UPDATE语句。

2.1、INSERT触发器

因为触发器是一种特殊类型的存储过程，所以创建触发器的语法格式与创建存储过程的语法格式相似。

当用户向表中插入新的记录行时，被标记为FOR INSERT的触发器的代码就会执行，同时SQL Server会创建一个新行的副本，将副本插入到一个特殊表中。该表只在触发器的作用域内存在。下面来创建当用户执行INSERT操作时触发的触发器。

在stu_info表上创建一个名为Insert_Student的触发器，在用户向stu_info表中插入数据时触发：

USE SQLDB; GO CREATE TRIGGER Insert_Student ON stu_info AFTER INSERT AS BEGIN     IF OBJECT_ID(N’stu_Sum’,N’U’) IS NULL                   —判断stu_Sum表是否存在         CREATE TABLE stu_Sum(number INT DEFAULT 0);         —创建存储学生人数的stu_Sum表     DECLARE @stuNumber INT;     SELECT @stuNumber = COUNT(*) FROM stu_info;     IF NOT EXISTS (SELECT * FROM stu_Sum)                   —判断表中是否有记录         INSERT INTO stu_Sum VALUES(0);     UPDATE stu_Sum SET number = @stuNumber                  —-把更新后总的学生人数插入到stu_Sum表中 END GO

 

上述程序语句的执行过程分析如下：

IF OBJECT_ID(N’stu_Sum’,N’U’) IS NULL                   —判断stu_Sum表是否存在 CREATE TABLE stu_Sum(number INT DEFAULT 0);         —创建存储学生人数的stu_Sum表

IF语句判断是否存在名为stu_Sum的表，如果不存在则创建该表。

DECLARE @stuNumber INT; SELECT @stuNumber = COUNT(*) FROM stu_info;

这两行语句声明一个整数类型的变量@stuNumber，其中存储了SELECT语句查询stu_info表中所有学生的人数。

IF NOT EXISTS (SELECT * FROM stu_Sum