资源简介

(共42张PPT)
网站数据库应用技术SQL Server 2008
（项目教程）
项目十 触发器
创建DML触发器
任务一
任务二
项目十 触发器
管理触发器
任务1 创建DML触发器
10.1.1 相关知识
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个数据库的表进行操作时，如insert-插入新记录，delete-删除记录， update-更新记录，就会激活它执行。
触发器就像一个开关，负责灯的亮与灭，你动了开关（触发器），灯的状态改变命令（触发器要执行的命令）就会被触发。你也可以把触发器理解为多米诺骨牌倒塌前抽掉的那张骨牌。
1.SQL Server2008的触发器
触发器经常用于加强数据的完整性约束和业务规则等。SQL Server 2008中，触发器类型包括DML触发器、DDL触发器及登录触发器三种类型。
（1）DML触发器
DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生DML事件时将启用。即如果用户要通过数据操作语言 (DML) 事件编辑数据，则执行 DML 触发器。DML事件即指在表或视图中修改数据的INSERT、UPDATE 或 DELETE语句。
（2）DDL触发器
DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生DDL事件时将启用。DDL事件即对应于 Transact-SQL CREATE、ALTER 和 DROP 语句，以及执行类似 DDL 操作的某些系统存储过程。
（3）登陆触发器
登陆触发器：是在遇到 LOGON 事件时触发。LOGON 事件是在建立用户会话时引发的。其中，DML是数据库应用中最常用的触发器，本章以DML触发器作为主要学习内容。
2.DML触发器执行的临时状态
在sql server 2008中，dml触发器的实现是基于两个临时的逻辑表（deleted表和inserted表），当触发执行dmls触发器时，这两个临时表将被创建，并存在于数据库服务器的内存中，我们只有只读的权限。
deleted和insered表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后，它们也就会被自动删除。
触发器分为后触发和替代触发两种方式。后触发为执行insert、update、delete等sql命令完成之后，再执行触发器的代码，后触发器只能创建在表上，不能创建在视图上。替代触发的特征是引起触发器执行的sql语句只起到启动触发器的作用，而且实际上没有执行，取而代之的是执行触发器中的sql语句，替代触发器可以建立在表上或者视图上。
（1）insered表
inserted表存放了insert和update语句中的副本。在insert或update语句的执行中，这些新行同时被加到inserted表和trigger表中。inserted表中的行是trigger表中新行的副本。比如你想向一个表插入一条新数据记录，那么触发器执行时，就会把这条新记录插入到inserted表中备用。
（2）deleted表
deleted表存放了delete和update语句中相关行的副本。在
delete或update语句的执行中，这些相关行从trigger表中移到了deleted表中。一般情况下，这两张表中无共同行。因此， deleted表用于存放操作 update、delete语句时，触发器所在的表中即将被删除或者即将被更新替换的数据。
一个update效果上等价于一个delete再接着一个insert。首先“旧”行被复制到deleted表中，然后新行被复制到trigger表和inserted表中
3.DML触发器语法
表或视图的DML触发器创建语法：
CREATE TRIGGER [schema_name.]触发器名
ON { 表|视图 }
[ WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[{IF UPDATE(列名)[{AND |OR} UPDATE(列名)][…n]}]
SQL语句块
参数说明：
WITH ENCRYPTION
对CREATE TRIGGER语句的文本进行模糊处理。使用WITH ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为CLR触发器指定WITH ENCRYPTION。
FOR|AFTER
AFTER指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
如果仅指定FOR关键字，则AFTER为默认值。不能对视图定义AFTER触发器。
参数说明：
INSTEAD OF
指定执行DML触发器而不是触发SQL语句，因此，其优先级高于触发语句的操作。不能为DDL或登录触发器指定INSTEAD OF。
SQL Server 2008对于表或视图，每个INSERT、UPDATE或DELETE语句最多可定义一个INSTEAD OF触发器。但是，可以为具有自己的INSTEAD OF触发器的多个视图定义视图。
INSTEAD OF触发器不可以用于使用WITH CHECKOPTION的可更新视图。如果将INSTEAD OF触发器添加到指定了WITH CHECKOPTION的可更新视图中，则SQL Server将引发错误。用户须用ALTER VIEW删除该选项后才能定义INSTEAD OF触发器。
参数说明：
{[DELETE][,][INSERT][,][UPDATE]}
指定数据修改语句，这些语句可在DML触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。
对于INSTEAD OF触发器，不允许对具有指定级联操作ON DELETE的引用关系的表使用DELETE选项。同样，也不允许对具有指定级联操作ON UPDATE的引用关系的表使用UPDATE选项。
NOTFORREPLICATION
指示当复制代理修改涉及到触发器的表时，不应执行触发器。
参数说明：
IF UPDATE(列名)
测试在指定的列上进行的INSERT或者UPDATE操作，不能用于DELETE操作，可以指定多列。因为已经在ON子句中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。若要测试中多个列上进行的INSERT或UPDATE操作，要分别单独地指定UPDATE(列名)子句。在INSERT操作中IF UPDATE将返回TRUE值。
注意：创建触发器时使用AFTER或者FOR关键字，创建的是后触发器，即当引起触发器执行的语句操作完成之后，并通过了各种约束检查之后，才执行触发器中的语句。
创建触发器时使用INSTEAD OF 关键字，创建的是替代触发器。此外，TRUNCATE TABLE语句的操作不被记录在事物日志文件，不会激发DELETE触发器。
10.1.2任务实施
1．创建INSERT触发器
任务目标：在Student数据库中创建一个触发器，当向成绩表插入一记录时，检查该记录的学号是否在学生表中存在，检查课程号在课程表中是否存在，若有一项不存在，则不允许插入成绩数据。
步骤1：进入SSMS管理界面后，在工具栏中单击“新建查询”按钮，打开“查询编辑器”窗口，如图10-1所示
步骤2：编写SQL命令，在查询编辑器窗口输入建立Student数据库命令，代码如下：
USE Student
GO
CREATE TRIGGER trig_check_cj_for_insert
ON 成绩表
FOR INSERT
AS
IF EXISTS(SELECT * FROM INSERTED a
WHERE a.学号 NOTIN(SELECT b.学号 FROM 学生表 b)
OR a.课程编号 NOTIN(SELECT c.课程编号 FROM 课程表 c))
BEGIN
RAISERROR('插入的数据不完整，违背数据一致性原则！',16,1)
END
GO
步骤3：执行以上代码，系统提示触发器创建成功之后，在查询编辑器窗口输入测试命令，代码如下：（注意，执行以下测试代码前，请先删除成绩表的外键约束）。
insert into成绩表(学号,课程编号,成绩) values ('01201401002','009',97)
系统提示如下：
2．创建UPDATE触发器
任务目标：当成绩表执行UPDATE操作时，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。
步骤1：创建UPDATE检查触发器的代码如下：
CREATE TRIGGER trig_check_cj_for_update
ON 成绩表
FOR update
AS
--若更新学号或者课程号，则抛出异常，并回滚事务，取消更新命令
IF UPDATE(学号) OR UPDATE(课程编号)
BEGIN
RAISERROR('成绩表的学号或者课程编号不能进行修改！',7,2)
ROLLBACK TRANSACTION
END
GO
步骤1：创建UPDATE检查触发器的代码如下：
CREATE TRIGGER trig_check_cj_for_update
ON 成绩表
FOR update
AS
--若更新学号或者课程号，则抛出异常，并回滚事务，取消更新命令
IF UPDATE(学号) OR UPDATE(课程编号)
BEGIN
RAISERROR('成绩表的学号或者课程编号不能进行修改！',7,2)
ROLLBACK TRANSACTION
END
GO
步骤2：测试语句代码如下：
UPDATE 成绩表 SET 课程编号=111,成绩=90
WHERE 学号='01201401001' AND 课程编号='001'
系统触发器拦截后，抛出异常消息，如图10-3所示。
3．创建DELETE触发器
任务目标：当从学生表中删除一个学生的记录时，应该相应的从成绩表删除该学生的所有成绩数据，以免造成成绩表存在垃圾数据。
步骤1：创建DELETE级联删除的触发器代码如下：
CREATE TRIGGER trig_aft_del_student
ON 学生表
AFTER DELETE
AS
DELETE FROM 成绩表 WHERE 学号=(SELECT 学号 FROM DELETED)

GO
步骤2：测试删除学生表的语句代码如下：
SELECT * FROM 学生表 --查看学生表数据
SELECT * FROM 成绩表 --查看成绩表数据
DELETE FROM 学生表 WHERE 学号=’01201401001’ -删除学生表的数据
SELECT * FROM 成绩表 --再次查看成绩表数据，检查触发器的影响
步骤2：测试语句代码如下：
UPDATE 成绩表 SET 课程编号=111,成绩=90
WHERE 学号='01201401001' AND 课程编号='001'
系统触发器拦截后，抛出异常消息，如图10-3所示。
任务2 管理触发器
10.2.1子任务1 删除触发器
1.用命令删除
使用 drop trigger 触发器名称来删除触发器。
也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称.
注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：
测试代码如下:
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='trig_aft_del_student' AND XTYPE='TR')
DROP TRIGGER trig_aft_del_student
2.用SSMS删除
在SSMS中，展开:学生表->触发器，选中所要删除的触发器，然后右键菜单中点击“删除”，如图所示
步骤8： 指定登录数据库服务器的账户信息设置
Windows身份验证模式
选择“Windows身份验证模式”，用户一旦登录到Windows，SQL Server就将使用信任连接。
混合模式
选择“混合模式”，即既可以使用Windows身份验证（如前面所述），也可以使用SQL Server身份验证，并且必须为内置 SQL Server系统管理员账户提供一个强密码。
sa（System Administrator）是默认的SQL Server超级管理员帐户，对SQL Server具有完全的管理权限。如果选择了“混合模式”身份验证，则必须为sa账户设置强密码。
10.2.2子任务2 重命名触发器
命令格式: exec sp_rename [原名称], [新名称]
sp_rename 是 SQL Server自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。
测试代码如下:
Exec sp_rename trig_aft_del_student,trig_aft_delete_student
10.2.3子任务3 查看数据库中的触发器
在查询窗口中运行：
USE Student
GO
SELECT * FROM SYSOBJECTS WHERE XTYPE='TR‘
sysobjects 保存着数据库的对象，其中 XTYPE为 TR 的记录即为触发器对象。
10.2.4子任务4 查看触发器内容
命令格式：
use 数据库名
go
exec sp_helptext '触发器名称‘
范例：
USE Student
GO
EXEC SP_HELPTEXT 'trig_check_cj_for_update'
以上命令将会以表的样式显示触发器内容，如图所示。
10.2.5子任务5 禁用、启用触发器
语法格式如下：
ALTER TABLE 表名
{ENABLE | DISABLE} TRIGGER
{ALL | 触发器名[,…n]}
如果有多个触发器，则各个触发器名称之间用英文逗号隔开。
如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。
实例代码如下：
ALTER TABLE学生表 DISABLE TRIGGER trig_check_cj_for_insert;
insert into成绩表(学号,课程编号,成绩) values ('01201401002','009',97)
项目小结
SQL Server 2008的DML触发器主要有插入触发器、更新触发器、删除触发器。本章主要讲述了SQL Server 2008数据库的DML触发器的基本概念和基本操作，以及触发器的常用管理功能。
任何一种对触发器表中数据进行修改的操作（包括操作人员录入数据或其他应用程序的修改）都能自动激活触发器，从而触发对这些操作进行的完整性检查。
触发器能够实施的检查和操作比主键和外键约束、check约束和规则对象等更为复杂。例如，check约束只能根据一个逻辑表达式或同一个表中其他列值来检查指定列值的有效性，而使用触发器时则可以参照其他表中的列值。
触发器也可以根据数据修改前后的表状态，再行采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。
触发器建立在表一级，它与特定的数据修改事件相对应。insert、update和delete三种操作都可能导致数据的修改，所以sql server中的触发器可分为 insert触发器、update触发器和delete触发器三种。
触发器只能由数据库所有者创建。因为当为某一个表或表的列、行创建触发器时，表的访问方式及其对象之间的关系也随之改变了。也就是说，触发器的创建变动了数据库模式，所以创建触发器的权利应该保留给数据库的所有者，以防止普通用户无意间修改了系统格局。
与此同时，虽然触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用？过多触发器会造成数据库及应用程序的维护困难，同时对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序，甚至会降低数据库的执行效率。
实训项目
步骤1： 创建数据库及表
步骤2：创建[T_INSERT_卷烟库存表]触发器
说明：每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。触发器功能：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。
注意： [INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。这两个系统表的结构同插入数据的表的结构。
实训项目
步骤3：针对【卷烟库存表】，插入测试数据
注意，第一条数据（红塔山新势力）中的数据符合业务规则，
第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则，
第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。
第四条数据库存数量为0。
请注意在插入数据后，检查【卷烟库存表】中的数据是否符合业务规则，即：库存金额 = 库存数量 * 库存单价。
由结果集可见，虽然插入的数据不符合业务逻辑规定，但是经过触发器的强制约束，使【卷烟库存表】中的：库存金额=库存数量*库存单价。
实训项目
步骤4：创建触发器[T_INSERT_卷烟销售表]
说明: 每当[卷烟销售表]发生 INSERT 动作，则引发该触发器。
触发器功能：实现业务规则。业务规则: 如果销售的卷烟品牌在【卷烟库存表】中不存在或者库存数量为零，则返回错误。否则则自动减少【卷烟库存表】中对应品牌卷烟的库存数量和库存金额。
步骤5：【卷烟销售表】数据插入测试
1.针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。
2.针对[卷烟销售表]，插入第二条测试数据不正常
3.针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在【卷烟库存表】中找不到对应数据。触发器将报错。
4.针对[卷烟销售表]，插入第四条测试数据，该数据中的卷烟品牌在【卷烟库存表】中库存为0。
步骤6： 查看最终结果
END！

展开更多......

收起↑