第10章 触发器1

►DELETED表保存删除的任何记录的副本
第19页
INSERTED表和DELETED表

这两个表的结构与触发器所在数据表的结构是完全 一致的，当触发器的工作完成之后，这两个表也将 会从内存中删除。 插入表(INSERTED)里存放的是更新前的记录：

►对于插入记录操作来说，插入表里存放的是要插 入的数据；
第28页
为什么不使用约束

根据正常的数据引用完整性，除非在Products表中 有匹配的ProductID，否则将不能向Order Details 表中输入任何Order Detail项 存货部门抱怨说，客户支持部门的人员不断发出已 经停止供应的产品的订单。应该在订单进入系统之 前拒绝这些订单的录入。 不能使用CHECK约束来处理，因为停止供应的状态 表(Products表)和放置约束的表(Order Details表) 是分离的。 使用触发器就可以跨表操作来解决这一问题


第14页
CREATE TRIGGER语法

After触发器：在记录已经改变完之后（after）， 才会被激活执行
►主要用于记录变更后的处理或检查一旦发现错误 可以用Rollback Transaction语句来回滚操作

Instead Of触发器：用来取代原本的操作，在记录 变更之前发生 ►并不去执行原来SQL语句里的操作（Insert、 Update、Delete），而去执行触发器本身所定 义的操作
第8页
DML触发器

与需要在表中显式调用代码的存储过程不同，只要 加在触发器上的事件在表中发生，触发器中的代码 就会自动地运行 ►不能显式地调用触发器——唯一的做法是执行指 派给表的所需的操作

除了不能够显式地调用触发器，与通常的存储过程 相比，触发器没有参数和返回值
第9页
DML触发器的类型

按照触发器事件类型的不同，可以把Microsoft SQL Server 2005系统提供的DML触发器分成3种类 型 ►INSERT类型


在SQL Server 2005中，根据SQL语句的不同，触 发器分为两类 ►一类是DML触发器
►一类是DLL触发器
第3页
触发器的类型

DML触发器：当数据库服务器中发生数据操作语言 （Data Manipulation Language）事件（包括在指 定表或视图中修改数据的INSERT语句、UPDATE 语句或DELETE语句）时执行的存储过程。


With Encryption：加密触发器
第13页
CREATE TRIGGER语法

{[INSERT][,]}[UPDATE][,][DELETE]：指定触发器 的类型: Insert触发器、Delete触发器，或Update触 发器 After可以用For来代取，代表只有在数据表的操作 都已正确完成后才会激活的触发器 INSERT、DELETE和UPDATE至少要指定一个，也 可以指定多个，若指定多个时，必须用逗号来分开。 其顺序可以任意摆放。
第4页
触发器常用的功能

完成比约束更复杂的数据约束
►触发器可以实现比约束更为复杂的数据约束

检查所做的SQL是否允许：触发器可以检查SQL所 做的操作是否被允许。
►例如：在产品库存表里，如果要删除一条产品记 录，在删除记录时，触发器可以检查该产品库存 数量是否为零，如果不为零则取消该删除操作。
第5页
►分为两类：After触发器和Instead Of触发器

DDL触发器：响应数据定义语言（Data Definition Language） (CREATE、ALTER、DROP或相似的 语句)对数据库结构进行修改的响应。 ►一般用于执行数据库中管理任务。如审核和规范 数据库操作、防止数据库表结构被修改等。
第10章 存储过程与触发器
第1页
10.3 触发器

Microsoft SQL Server 2005系统提供了两种强制业 务逻辑和数据完整性的机制，即约束技术和触发器 技术。
第2页
触发器的概念

触发器实际上就是一种特殊类型的存储过程，该存 储过程响应特定的事件 ►在执行某些特定的T-SQL语句时自动执行 与触发器不同，存储过程可以由用户直接调用执行


第25页
为什么不使用约束

为了数据完整性规则使用触发器 ►触发器也同样可以完成像CHECK约束甚至是 DEFAULT约束一样的功能。 ►有时会出现CHECK约束不能完成任务的情况，或 者是当触发器比从CHECK约束更满足要求
►想要使用触发器而非CHECK约束的示例包括：
• 业务规则需要检查更新的增量(更新前后的区别) • 需要一个定制的错误消息
第26页
为什么不使用约束

处理来自于其他表的需求
►CHECK约束快速而且有效，但是它们不能做所 有的事情。当需要在表之间验证数据时，它最大 的缺点会暴露出来。 ►看一下Northwind数据库中的Products和Order Details表
第27页
为什么不使用约束

Northwind数据库中的Products和Order Details表。 它们的关系如图所示
►约束是不能返回信息的，而触发器可以
►例如插入一条重复记录时，可以返回一个具体的 友好的错误信息给前台应用程序

更改原本要操作的SQL语句：触发器可以修改原本 要操作的SQL语句 ►例如原本的SQL语句是要删除数据表里的记录， 但该数据表里的记录是重要记录，不允许删除的， 那么触发器可以不执行该语句。
第17页
UPDATE触发器

每当对表中现有的记录进行修改时，都会激活在触 发器中声明为FOR UPDATE的代码。 SQL Server会把每一行当成好像删除了现有的记录， 并插入了全新的记录一样


声明为FOR UPDATE的触发器包含了两个特殊的表， 称为INSERTED和DELETED
这两个表有完全相同数量的数据行
第23页
为什么不使用约束

在SQL Server 2005里，可以用两种方法来保证数 据的有效性和完整性 ►约束（check）和触发器（Trigger） 约束是直接设置于数据表内，只能现实一些比较简 单的功能操作，如： ►实现字段有效性和唯一性的检查、自动填入默认 值、确保字段数据不重复（即主键）、确保数据 表对应的完整性（即外键）等功能

第22页

例10-7 为表customer创建一个名为test_tr的触发 器，当执行添加、更新或删除时，激活该触发器。
CREATE TRIGGER test_tr ON customer FOR INSERT,UPDATE,DELETE
AS
SELECT * FROM inserted
SELECT * FROM deleted

第24页
为什么不使用约束

触发器是针对数据表（库）的特殊的存储过程，当 这个表发生了Insert、Update或Delete操作时，会 自动激活执行的，可以处理各种复杂的操作。 在SQL Server 2005中，触发器有了更进一步的功 能，在数据表（库）发生Create、Alter和Drop操作 时，也会自动激活执行 能用约束的地方尽量使用约束，单用约束无法实现 的要求才使用触发器


触发器可以检查inserted表，来确定该触发器的操 作是否应该执行和如何执行。
第16页
DELETE触发器

和INSERT触发器的工作方式相同，除了INSERTED 表会变空之外(对于INSERTED表来说没有记录) 每个删除的记录的副本将插入到另一个表中，该表 称为DELETED


和INSERTED表类似，该表将只限于触发器的作用 域范围内
►对于更新记录操作来说，插入表里存放的是要更 新的记录。
第20页
INSERTED表和DELETED表

对于更新记录操作来说，删除表里存放的是更新前 的记录（更新完后即被删除） 对于删除记录操作来说，删除表里存入的是被删除 的旧记录。
Inserted表 存放要插入的记录 Deleted表

激活触发器的动作 Insert

第12页
CREATE TRIGGER语法

CREATE TRIGGER trigger_name：声明SQL语句 是用来建立一个触发器。其中触发器名在所在的数 据库里必须是唯一的 ON子句来指出触发器将要加在其上的表，以及在何 时和何种情况下激活这个触发器。 ►ON{table|view}指定触发器所在的数据表或视图 ►每个触发器都只能附着在一个表或视图上
第11页
CREATE TRIGGER语法
触发器需要定义在一个表上 CREATE TRIGGER <trigger name> ON <table | view > [WITH ENCRYPTION] {{{FOR|AFTER} <[DELETE] [,] [INSERT] [,] [UPDATE]>} |INSTEAD OF} AS [{IF [UPDATE (column) [{AND|OR}UPDATE(column)]]] COLUMNS_UPDATE()] sql statements}}
第29页



为什么不使用约束
CREATE TRIGGER OrderDetailNotDiscontinued ON [Order Details] FOR INSERT, UPDATE AS IF EXISTS (SELECT 'True' FROM Inserted i JOIN Products p ON i.ProductID = p.ProductID WHERE p.Discontinued = 1)

AS子句：告诉SQL Server将要启动的代码
第15页
DML触发器的工作原理

INSERT触发器的工作原理


当向表中插入数据时，INSERT触发器触发执行
当INSERT触发器触发时，新的记录增加到触发器表 中和inserted表中。 该inserted表是一个逻辑表，保存了所插入记录的 拷贝，允许用户参考INSERT语句中数据。
Update
Delete
存放要更新的记录
存放更新前的旧记录
存放要删除的旧记录
第21页
例10-6 在employee表上创建一个DELETE类型的 触发器，该触发器的名称为tr_employee。 CREATE TRIGGER tr_employee ON employee FOR DELETE AS DECLARE @msg varchar(50) SELECT @msg=STR(@@ROWCOUNT)+'个员工被 删除' SELECT @msg RETURN
触发器常用的功能

修改其它数据表里的数据：
►当一个SQL语句对数据表进行操作的时候，触发 器可以根据该SQL语句的操作情况来对另一个数 据表进行操作。 ►例如：一个订单取消的时候，那么触发器可以自 动修改产品库存表，在订购量的字段上减去被取 消订单的订购数量。
第6页ቤተ መጻሕፍቲ ባይዱ
触发器常用的功能

返回自定义的错误信息
►UPDATE类型
►DELETE类型
►以上任何类型的混合和匹配
第10页
10.4 创建DML触发器

DML触发器是一种特殊类型的存储过程，所以DML 触发器的创建和存储过程的创建方式有很多相似的 地方。 使用CREATE TRIGGER语句创建DML触发器。


在CREATE TRIGGER语句中，指定了定义触发器 的基表或视图、触发事件的类型和触发的时间、触 发器的所有指令等内容。

第18页
对逻辑表进行删除和插入

在SQL Server里，为每个DML触发器都定义了两个 特殊的表，一个是插入表，一个是删除表。 这两个表建在数据库服务器的内存中，是由系统管 理的逻辑表，不是真正存储在数据库中的物理表


对这两个表用户只有读取权限，没有修改权限
►INSERTED表保存插入记录的副本
第30页
为什么不使用约束
BEGIN
RAISERROR('Order Item is discontinued. Transaction Failed.',16,1)
ROLLBACK TRAN
END
第31页
触发器嵌套

任何触发器都可以包含影响另外一个表的INSERT、 UPDATE或DELETE语句。 当允许触发器嵌套时，一个触发器可以修改触发第 二个触发器的表，第二个触发器又可以触发第三个 触发器。 触发器可以激活的深度取决于： ► 系统是否允许触发器嵌套 ► 是否有嵌套32级深度的限制 ► 触发器是否已经激活