sql server 2008案例教程及实训 第11章 触发器

DELETE FROM BorrowList WHERE 图书编号=@BookNo
END
【例11.2】创建一个简单的DML触发器，当读者还书的时候， 计算其逾期罚款。
Create TRIGGER [dbo].[update_up] ON [dbo].[BorrowList] AFTER UPDATE
触发器是一个在修改指定表中的数据时 执行的存储过程。经常通过创建触发器来强 制实现不同表中的逻辑相关数据的引用完整 性或者一致性。当对某一表进行诸如 UPDATE、INSERT、DELETE 这些操作时， SQL Server 就会自动执行触发器所定义的 SQL 语句
11.1 触发器简介
11.1.1 触发器的概念 11.1.2 触发器的类型
SET NOCOUNT OFF --不返回结果 DECLARE @delcount INT DECLARE @BookNo CHAR(6)
-------------------更新（级联更新）--------------------IF UPDATE(图书编号)
BEGIN
UPDATE BorrowList SET 图书编号=(SELECT 图书编号 FROM inserted) WHERE 图书编号 in (SELECT 图书编号 FROM deleted)
可以指定第一个和最 后一个触发器执行动 不适用 作
不允许
允许
（1）DML触发器
在建立DML触发器时，①要指定触发操作：insert、
update或delete。至少要指定一种操作，也可以同时
指定多种。②在同一个表中可以创建多个after触发
器，但在表或视图上，每个insert、update或delete 语句最多可以定义一个instead of触发器。
WHERE 图书名称=‘电脑乐园’
消息如下：
更新BookInfo表的数据
【例2】 创建级联删除的触发器，当有人试图 删除BookInfo表的信息时，利用触发器级联删除 与之相关的BorrowList表中的信息。
CREATE TRIGGER triDelete_Book ON BookInfo FOR DELETE AS BEGIN
--测试触发器 update BorrowList set 实际归还时间='2013-08-23' where 借阅编号='9'
11.3.2 修改触发器
alter trigger [所有者.]触发器名称 on {[所有者.]表名|视图} {for|after|insteadof} {[insert][,][update][,][delete]} [not for replication] as
在“对象资源管理器”中，展开数据库目录，找到需要 修改触发器的表，展开表目录，展开触发器节点目录， 右击需要删除的触发器，如图11.5所示，单击快捷菜单 中的“删除”命令。
11.3 使用T-SQL创建和使用触发器
create用triCgRgEeArT触E 发TR器I名GG称ER语句创建DML触发器， o要n {注表意名|的视是图}该语句必须是批处理中的第一条 {f语or句|af，ter并|in且ste只ad能o应f} 用于一个表。CREATE { T[iRnIsGeGrEt R][语,][u句p的da部te]分[,]语[d法ele格te式] }如下：
【例4】在BookInfo表和BorrowList表之间具有逻辑 上的主外键关系，要求当删除或更新图书信息的时候， 要激发触发器tri_Update_Delete ，并在BorrowList表 中删除或更新相对应的记录行 。
CREATE TRIGGER tri_Update_Delete ON BookInfo FOR UPDATE, DELETE AS
（2）DDL触发器
DDL触发器是响应create、alter、drop、grant、 deny、revoke 和 update statistics 等语句而触发的。
11.2 界面中创建和使用触发器
11.2.1 创建触发器 11.2.2 修改触发器 11.2.3 删除触发器
11.2.1 创建触发器
11.1.2 触发器的类型
1．类型
根据触发事件的不同，将触发器分成两大类： DML触发器和DDL触发器。
（1）DML触发器。DML触发器是一种与表紧密关联的特殊 的存储过程，当数据库中发生数据操作语言（DML）事件 （Insert、Update、Delete）时将调用DML触发器。
● AFTER触发器 ● INSTEAD OF触发器
11330011
update sell set 数量=22 where 销售编号=5
deleted表
销售 编号
商品 编号
数量
售出时间
5 7 2 2005-1-3
售货员 工编号
1301
t_score表
S_number C_number Score credit
040101
1
040101
5
040101
11.1.1 触发器的概念
触发器是一种特殊的存储过程，在语言事件发 生时，所设置的触发器就会自动被执行，以进行维 护数据完整性或其他一些特殊的任务。
与上一章介绍的一般意义的存储过程不同，触 发器是当发生UPDATE、INSERT、DELETE 这些操作 时自动执行的存储过程。不能直接被调用，也不能 传递或接受参数。
人试图更新BookInfo表的信息时，利用触发器产
生提示SE 图书借阅信息数据库 GO CREATE TRIGGER tri_UpdateBooks
Instead of 改为 For
ON BookInfo
IAPnrSsinteta‘d更O新f UBPoDokAITnEfo表的数据'-USE-PT测D作试A者T触=E发‘B谭器oo宇k之In’fo
（2）DDL触发器。DDL触发器是在响应数据定义语言 （DDL）语句时激发。
这些语句主要是以CREATE、ALTER和DROP开头的 语句。DDL触发器可用于管理任务，例如审核和控制数据库 操作。
功能
适用对象 每个表或视图 可用的数量 级联引用
执行时机
执行顺序
在inserted表和deleted 表引用text、ntext和 image类型的数据
1
2
1
2004-10-15 1301
2
2
1
2004-10-16 1302
3
5
2
2004-10-26 1303
4
6
1
2005-3-20 1301
5 7 222 2005-1-3 1301
inserted表
销售 编号
商品 编号
数量
售出时间
55 77 222 22000055--11--33
售货员 工编号
6
040101
7
040102
2
88
NULL
92
NULL
85
NULL
88
NULL
73
NULL
Delete form t_score where S_number='040102'
deleted表
S_number C_number Score credit
040102
2
73
NULL
【例1】 创建一个简单的DML替代触发器，当有
after 触发器
instead of触发器
表
表和视图
允许每个动作有 多个触发器
每个动作（update、delete 和insert）一个触发器
没有限制
在作为级联引用完整性约束 目标的表上限制应用
声明引用动作之后
在约束处理之前，代替了触 发动作
在创建inserted表和 在inserted表和deleted表创 deleted表触发时 建之后
DELETE FROM BorrowList
WHERE 图书编号 in (select图书编号 from deleted)
END
--测试触发器 Delete Form BookInfo
WHERE 图书编号=‘1008-2352’
消息如下：
* 行受影响
【例3】创建一个AFTER INSERT触发器，当在 ReaderCard表中插入一条新记录时，如果卡类型 不是“Student、Teacher、Worker、Manager或 other”时，则撤销该插入操作，并返回出错消 息。
【例11.1】创建一个简单的DML触发器，当读者完成借书 手续后，系统中的库存数量要及时更新。
图11.1 新建触发器图
11.2 新建触发器模板
图11.3 “指定模板参数的值”对话框 图 11.4 触发器代码
11.2.2 修改触发器
图11.5 修改触发器
图11.6 “修改触发器代码”窗口
11.2.3 删除触发器
第11章 触发器
11.1
触发器简介
11.2
界面中创建和使用触发器
11.3
用代码创建和使用触发器
与存储过程类似，触发器也是SQL 语句 集，两者惟一的区别是触发器不能用 EXECUTE 语句调用，而是在用户执行 Transact-SQL 语句时自动触发（激活）执 行，所以我们触发器就是一种特殊的存储过 程。
rollback transaction --返回用户定义的错误信息，事务回滚
print '不能插入不合法类型！' end
--测试触发器
insert into ReaderCard values (‘12121212’,‘章一', ‘男’,‘1985-8-8’,‘2003-9-9’,‘正常’,‘卡类型')