SQL第10章11章事务与触发器

3
10.1 程序中的事务
自动提交事务 每条单独的SQL语句都是一个事务，这是 SQL默认的事务管理模式，每个T-SQL语句完成 时，都被（成功）提交或（失败）回滚。 批处理级事务 只能应用于多个活动结果集（MARS），在 MARS会话中启动的T-SQL显示或隐式事务变为 批处理集事务。当批处理完成时没有提交或回 滚的批处理级事务将自动由SQL SERVER进行回 滚。
13
10.4 触发器的创建执行
10.4.1 使用SQL语句创建触发器 10.4.2 创建INSERT 触发器 10.4.3 创建DELETE 触发器 10.4.4 创建UPDATE 触发器 10.4.5 创建INSTEAD OF 触发器
14
10.4.1 使用SQL语句创建触发器
1）语法格式为： CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [DELETE][,][ UPDATE ] } AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] | sql_statement [ ...n ] } 15 }
在定义了 INSERT 触发器的表上执行 INSERT 语句 触发器动作被执行
INSERT [Order Details] VALUES 触发器代码： 2, 19.00, 5, 0.2) USE Northwind 1 (10523, 在定义了 INSERT 触发器的表上 CREATE TRIGGER OrdDet_Insert ON [Order Details] 执行 INSERT 语句 Details FOROrder INSERT AS OrderID ProductID UnitPrice Quantity Discount 2 INSERT UPDATE 语句插入的行被记录下来 UPDATE P SET SET P 10522 2= 31.00 7 0.2 UnitsInStock = (P.UnitsInStock (P.UnitsInStock – I.Quantity) UnitsInStock – I.Quantity) FROM Products AS P INNER INNER JOIN Inserted AS AS II 10523 41 AS 9.65 9 JOIN 0.15 FROM Products P Inserted 3 触发器动作被执行 ON P.ProductID P.ProductID = I.ProductID ON 10524 7 = I.ProductID 30.00 24 0.0
10.2 事务的应用
BEGIN TRAN INSERT authors VALUES(@au_id, @au_lname, @au_fname, @phone, @address, @city, @state, @zip, @contract) IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN END
19
10.4.3 DELETE 触发器
例3：在northwind数据库中的categories表 上创建一个delete触发器，以保证当将某 一个类别删除的同时将products表中对应 categoryId的discontinued列的值设置为1
20
10.4.3 DELETE 触发器
在定义了 DELETE 触发器的表上执行 DELETE 语句 触发器动作被执行
10.4.2 INSERT 触发器
例2：在northwind数据库中的order details表上创建一个触发器,使得无论何 时向order details表中插入一条记录， 都将更新product表中的unitsInstock列， 即用原来的数量减去订购的数量。
17
10.4.2 INSERT 触发器
8
同步任务
同步任务参见实训教程p204页[同步任务1-1] [同步任务1-2] [同步任务1-3]
9
10.3 触发器综述
1.触发器概念 触发器是一类特殊的存储过程，被定义为在对特定表或 视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行. 2.触发器的特点：
它与表紧密相连，可以看作表定义的一部分； 它不能通过名称被直接调用，更不允许带参数，而是当用
10.4.2 INSERT 触发器
INSERT 触发器的工作过程 – 在定义了 INSERT 触发器的表上执行 INSERT 语句 – INSERT 语句插入的行被记录下来 – 触发器动作被执行 inserted 表 – 触发 INSERT 触发器时，新行被同时增加到触发器表 和 inserted 表中 – inserted 表是保存了插入行的副本的逻辑表，它并不 实际存在于数据库中 – inserted 表允许用户引用 INSERT 语句所插入的数据， 这样触发器可以根据具体数据决定是否执行以及如何执 16 行特定语句
第10章 事务与触发器
10.1 程序中的事务 10.2 事务的应用 10.3 触发器综述 10.4 触发器的创建执行 10.5 查看触发器信息 10.6 修改和删除触发器
1
10.1 程序中的事务
1. 概述
1）事务 事务是最小的工作单元。这个工作单元要么成功完成 所有操作，要么就是失败，并将所做的一切复原。 2）事务特性
12
10.3 触发器综述
5、DML触发器的种类 两种类型：AFTER触发器和 INSTEAD OF触发器。 1）AFTER触发器 AFTER触发器又称为后触发器，该类触发器是在引 起触发器执行的修改语句成功完成之后执行。 2） INSTEAD OF触发器 INSTEAD OF触发器又称为替代触发器，当引起触 发器执行的修改语句停止执行时，该类触发器代替触发 操作执行。
户对表中的数据进行编辑时，自动执行；
它可以用于SQL
Server约束、默认值和规则的完整性检查， 实施更为复杂的数据完整性约束。
10
10.3 触发器综述


3、触发器的用途 在数据库中的相关表上实现级联更改 – 在数据库的相关表上使用触发器可实现级联更新或删除 强制比 CHECK 约束更复杂的数据完整性 – 和 CHECK 约束不同，触发器可以引用其他表中的列 – 通过下列方法使用触发器来强制复杂的引用完整性：根据情况确定 是否级联更新与删除、创建多行触发器、在数据库间强制引用完整 性 维护非标准数据
Categories
CategoryID CategoryName Description Picture 1 在定义了 DELETE 触发器的表上 DELETE 语句删除的行被记录下来 DELETE Categories 1 Beverages Products Soft drinks, coffees… 0x15… Deleted 执行 DELETE 语句 WHERE … categoryid 2 0x15… Condiments ProductID Sweet Discontinued and savory … 0x15… Dairy Products 4 Cheeses CategoryID = 4 3 Confections Desserts, candies, … 0x15… 1 0 2 DELETE 语句删除的行被记录下来 2 0 4 Dairy Products Cheeses 0x15… 2 1 4 USE Northwind 3 0 CREATE TRIGGER Category_Delete 3 触发器动作被执行 4 0 ON Categories
4
10.2 事务的应用
例1:请用事务在PUBS数据库中创建一个存储过程 pr_auth_user,当向authors表中插入一个作者信息，同时 将该作者的姓名插入到用户表中的username列，pwd列的 初始值为用户名。 实现的T-SQL语句: CREATE TABLE usremember (username varchar(80), Pwd varchar(50), Email varchar(50), Phone char(12)) GO
5
10.2 事务的应用
CREATE PROC pr_auth_user @au_id varchar(11), @au_lname varchar(40), @au_fname varchar(40), @phone char(12), @contract bit, --一般将具有默认值的参数放在后边定义，便于调用时 省略 @address varchar(50)=null, @city varchar(20)=null, @state char(2)=null, @zip char(5)=null 6 AS

– 触发器可以用来在非标准数据库环境中维护底层的数据完整性。非 标准数据常常是人为得出的或冗余的数据值

比较数据修改前后的状态 – 大部分触发器提供了引用被修改数据的能力，这样就允许用户在触 11 发器中引用正被修改语句所影响的行
10.3 触发器综述
4、 触发器的类型 DML触发器和DDL触发器。 DML触发器 DML触发器是当数据库服务器中发生数据操纵语言（DML） 事件，如INSERT、UPDATE、DELETE时所触发的操作。DML 触发器用于在数据被修改时强制执行业务规则。注意：SELECT语 句不能触发触发器操作，因为SELECT语句并没有修改表中的数 据。 DDL触发器 SQL SERVER 2005中新增了DDL触发器，该触发器是一种 特殊的触发器，当服务器或数据库中发生数据定义语句（DDL） 事件，如CREATE、ALTER、DROP数据库对象等操作时将调用 这些触发器。它们可以用于在数据库中执行管理任务，如审核以 及规范数据库操作。
7
10.2 事务的应用
INSERT usermember(username,pwd) VALUES(@au_fname+' '+ @au_lname, @au_fname+' '+ @au_lname) IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN End COMMIT TRAN GO 测试语句： Exec pr_auth_user '111-11-1111', 'zhang', 'hai', '101 110-0001', 1, 'hfdjfdjkfdjkfd', 'changchun', 'CA', '12345' GO
原子性（Atomic）
一致性（ConDemoltent） 独立性（Isolated）
持久性（Durable）
2
10.1 程序中的事务
3）事务类型 显式事务：显示事务是手工配置的事务。用保留字标识显 式事务的开始和结束。
开始显式事务，使用BEGIN TRAN。 结束显示事务，使用COMMIT TRAN。 取消事务，使用ROLL BACK TRAN命令。 隐式事务 ：在前一个事务完成时新事务隐式启动，但每个 事务仍以COMMIT或ROLLBACK语句显示完成。
Order Details
OrderID ProductID UnitPric3
2
19.00
0.2 5 Products
ProductID UnitsInStock … …
10522 2 31.00 7 0.2 10523 41 语句插入的行被记录下来 9.65 9 0.15 INSERT 10524 7 30.00 24 0.0 inserted 0.2 10523 2 19.00 5 10523 2 19.00 5 INSERT 语句插入的行被记录下来 inserted 10523 2 19.00 5 0.2
0.2
1 2 3 4
15 10 15 65 20
18
10.4.3 DELETE 触发器
DELETE 触发器的工作过程 – 在定义了 DELETE 触发器的表上执行 DELETE 语句 – DELETE 语句删除的行被记录下来 – 触发器动作被执行 deleted 表： – 触发 DELETE 触发器时，被删除的行放入 deleted 表中 – deleted 表是保存了被删除行的副本的逻辑表 – deleted 表允许用户引用 DELETE 语句所删除的数据 使用 DELETE 触发器 – 当行添加到 deleted 表后，将不再存在于数据库表中 – 从内存中分配空间创建 deleted 表。deleted 表总在缓存中 – DELETE 触发器不会被 TRUNCATE TABLE 语句触发