10_触发器和事务

6、写入B帐户金额11000
Ver. 1.0
第14章
幻灯片19（共24张）
使用SQL Server 2005查询和管理数据
• 数据库引擎 提供： – 锁定设备，使事务保持隔离。 – 记录设备，保证事务的持久性。即使服 务器硬件、操作系统或数据库引擎 实例 自身出现故障，该实例也可以在重新启 动时使用事务日志，将所有未完成的事 务自动地回滚到系统出现故障的点。 – 事务管理特性，强制保持事务的原子性 和一致性。事务启动之后，就必须成功 完成，否则数据库引擎 实例将撤消该事 务启动之后对数据所做的所有修改。
第14章
幻灯片3（共24张）
使用SQL Server 2005查询和管理数据
触发器工作原理
DML触发器的工作原理
驻留在内存中 用户不能修改 表的结构与触发器作用的表的结构相同 触发器工作完成以后，表删除
SQL Server 2005 会自动创建和管理 两个表： Inserted表和Deleted表
课间思考 你想对在另一个数据库对象中进行修改，无论何时任何新数 据库对象被创建的时候。你将使用下面哪个触发器?
1. 2. 3. 4. DML触发器 替代触发器 DDL触发器 嵌套触发器
答案：
3. DDL 触发器
Ver. 1.0
第14章
幻灯片9（共24张）
使用SQL Server 2005查询和管理数据
INSTEAD OF 触发器
当要执行DML操作时，用别的操作代替 不能为表或视图的DML操作创建多余一个的 INSTEAD OF 触发器
Ver. 1.0
第14章
幻灯片7（共24张）
使用SQL Server 2005查询和管理数据
触发器分类
嵌套触发器
一个触发器执行启动另一个触发器的操作 DML 触发器和 DDL 触发器最多可以嵌套 32 层
Ver. 1.0
第14章
幻灯片20（共24张）
使用SQL Server 2005查询和管理数据
创建事务
有以下类型：
自动提交事务(Autocommit trsnsaction) 显式事务(Explicit transactions)
自动提交事务：
自动提交事务是SQL Server的默认事务管 理模式
触发器分类
AFTER 触发器 ：
在执行了 INSERT、UPDATE 或 DELETE 语句操 作之后执行 AFTER 触发器,只有在成功执行触发 SQL 语句之后，才会执行 AFTER 触发器 如果仅指定 FOR 关键字，则 AFTER 为默认值 AFTER 触发器只能在表上指定 可以为任何一个DML操作定义多个AFTER触发器
识别触发器类型
触发器有以下类型：
DML 触发器
当关联表被DML影响的时候，触发
DDL 触发器
DDL影响的时候触发 DDL 触发器是 SQL Server 2005 的新增功能 用于管理任务
触发器的限制
– CREATE TRIGGER 必须是批处理中的第一条 语句，并且只能应用于一个表。 – 触发器只能在当前的R语句来创建触发器 语法： CREATE TRIGGER trigger_name ON { OBJECT NAME } { FOR | AFTER | INSTEAD OF } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS } { AS { sql_statement [ ...n ] } } 创建两个称为魔表的临时表
Ver. 1.0
第14章
幻灯片18（共24张）
使用SQL Server 2005查询和管理数据
1、读取A帐户金额5000
2、A帐户金额＝5000－1000
3、写入A帐户金额4000
如果在这个阶段发生 故障，A＝4000，B ＝10000
4、读取B帐户金额10000
5、B帐户金额＝10000＋1000
删除触发器：
语法：
DROP TRIGGER { trigger }
让我们看看如何…
Ver. 1.0
第14章
幻灯片12（共24张）
使用SQL Server 2005查询和管理数据 ALTER TRIGGER HumanResources.trgInsertShift ON HumanResources.Shift FOR INSERT AS DECLARE @ModifiedDate datetime SELECT @ModifiedDate = ModifiedDate FROM Inserted IF (@ModifiedDate != getdate()) BEGIN RAISERROR (’The modified date is not the current date. The transaction cannot be processed.',10, 1) ROLLBACK TRANSACTION END RETURN
UPDATE操作时触发器的工作
相当于插入一条新纪录和删除一条旧记录 表中原有的记录存放在deleted表中 修改后的记录存放在inserted表中
Ver. 1.0
第14章
幻灯片5（共24张）
使用SQL Server 2005查询和管理数据
触发器
创建 DML 触发器时需指定：
– 名称。 – 定义触发器时所基于的表。
Ver. 1.0
第14章
幻灯片22（共24张）
使用SQL Server 2005查询和管理数据
• BEGIN TRANSACTION
– 标记一个显式本地事务的起始点。BEGIN TRANSACTION 使 @@TRANCOUNT 按 1 递增。 – 如果遇上错误，在 BEGIN TRANSACTION 之后的所有数 据改动都能进行回滚，以将数据返回到已知的一致状态。每 个事务继续执行直到它无误地完成并且用 COMMIT TRANSACTION 对数据库作永久的改动，或者遇上错误并 且用 ROLLBACK TRANSACTION 语句擦除所有改动。 – BEGIN TRANSACTION 为发出本语句的连接启动一个本地 事务。根据当前事务隔离级别的设置，为支持该连接所发出 的 Transact-SQL 语句而获取的许多资源被该事务锁定，直 到使用 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句完成该事务为止。
让我们看看如何…
Ver. 1.0
第14章
幻灯片10（共24张）
使用SQL Server 2005查询和管理数据
CREATE TRIGGER [HumanResources].[trgDepartment] ON [HumanResources].[Department] AFTER UPDATE AS BEGIN UPDATE [HumanResources].[Department] SET [HumanResources].[Department].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[DepartmentID] = [HumanResources].[Department].[DepartmentID]; END;
在隐式模式中，下面的语句触发一个事务：ALTER TABLE, INSERT, OPEN, CREATE, DELETE, REVOKE, DROP, SELECT, FETCH, TRUNCATE TABLE, GRANT, UPDATE。
@@TRANCOUNT :返回当前连接的活动事务数。
BEGIN TRANSACTION 语句将 @@TRANCOUNT 加 1 ROLLBACK TRANSACTION 将 @@TRANCOUNT 递减到 0 COMMIT TRANSACTION 将 @@TRANCOUNT 递减 1。 trancount例句
使用SQL Server 2005查询和管理数据
目标
在本章中，你将学到： 实现触发器 触发器是一种特殊的存储过程 触发器不能在系统表上创建。 触发器不像存储过程，不能返回值或结果集 实现事务 维护数据的完整性
Ver. 1.0
第14章
幻灯片1（共24张）
使用SQL Server 2005查询和管理数据
递归触发器
直接递归 ：在触发器触发并执行一个导致同 一个触发器再次触发的操作时，将发生此递 归 间接递归 ：触发器触发并执行一个触发同一 个表或另一个表的另一个触发器的操作时， 将发生此递归。第二个触发器执行一个再次 触发第一个触发器的操作
Ver. 1.0
第14章
幻灯片8（共24张）
使用SQL Server 2005查询和管理数据
创建事务
事务（transaction)：——保证数据完整 性
将一组语句作为一个单元执行 必须拥有称为ACID的四个属性
原子性（Atomicity）：事务必须是原子工作单元； 对于其数据修改，要么全都执行，要么全都不执 行。 一致性（Consistency）：事务在完成时，必须使 所有的数据都保持一致状态。 隔离性（Isolation）：一个事务的执行不会被另一 个事务干扰 持久性（Durability）：事务完成之后，它对于系 统的影响是永久性的。该修改即使出现系统故障 也将一直保持。
Ver. 1.0
第14章
幻灯片4（共24张）
使用SQL Server 2005查询和管理数据
触发器工作原理
INSERT操作时触发器的工作
插入数据时，insert触发器将被触发执行，新的记 录被增加到触发器表和inserted表中
DELETE操作时触发器的工作
级联：主表删除，依赖表中也删除 限制：如果相关记录在依赖表中存在，限制从主表 中删除 无效：主表中删除，在依赖表中的相关列中置为无 效
显式事务：
显式事务使用BEGIN TRANSACTION 和 COMMIT TRANSACTION语句指定
让我们看看如何…
Ver. 1.0
第14章
幻灯片21（共24张）
使用SQL Server 2005查询和管理数据
创建事务
隐式和显示模式之间切换 ： SET IMPLICIT_TRANSACTIONS {ON | OFF}
Ver. 1.0
第14章
幻灯片13（共24张）
使用SQL Server 2005查询和管理数据
课间思考 指出当一个触发器响应INSERT, DELETE 或 UPDATE语句 创建的表的名称。
答案：
魔表 – 已插入和已删除表
Ver. 1.0
第14章
幻灯片14（共24张）
使用SQL Server 2005查询和管理数据
Inserted表：用于存储 INSERT 和 UPDATE 语句所影响
的行的副本。在插入或更新事务期间，新行将同时被添加到 插入的表和触发器表。插入的表中的行是触发器表中的新行 的副本。
Deleted表：用于存储 DELETE 和 UPDATE 语句所影响
的行的副本。在执行 DELETE 或 UPDATE 语句的过程中， 行从触发器表中删除，并传输到删除的表中。删除的表和触 发器表通常没有相同的行。
Ver. 1.0
第14章
幻灯片11（共24张）
使用SQL Server 2005查询和管理数据
管理触发器 改变触发器：
语法： ALTER TRIGGER trigger_name { FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS } { AS { sql_statement [ ...n ] } }
Ver. 1.0
第14章
幻灯片2（共24张）
使用SQL Server 2005查询和管理数据
识别触发器类型
DML触发器的特性 自动触发，在数据修改语句提交的时候 不能被显式调用或执行 防止错误、未授权和数据的不一致 不能返回数据给用户 可以嵌套到32层
示例：trgDeleteDepartment
Ver. 1.0
– 触发器被触发的时间。
– 激活触发器的数据修改语句。有效选项为 INSERT、 UPDATE 或 DELETE。多个数据修改语句可激活 同一个触发器。例如，触发器可由 INSERT 或 UPDATE 语句激活。 – 执行触发器操作的编程语句。
Ver. 1.0
第14章
幻灯片6（共24张）
使用SQL Server 2005查询和管理数据