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

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
19
10.4.3 DELETE 触发器
例3：在northwind数据库中的categories表 上创建一个delete触发器，以保证当将某 一个类别删除的同时将products表中对应 categoryId的discontinued列的值设置为1
20
10.4.3 DELETE 触发器
在定义了 DELETE 触发器的表上执行 DELETE 语句 触发器动作被执行
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
在定义了 INSERT 触发器动作被执行 触发器的表上执行 INSERT 语句
INSERT [Order Details] VALUES 触发器代码： USE 5, 0.2) 1 (10523, 2, 19.00,Northwind 在定义了 INSERT 触发器的表上 CREATE TRIGGER OrdDet_Insert ON [Order Details] 执行 INSERT 语句 FOROrder Details INSERT AS OrderID ProductID UnitPrice Quantity Discount 2 INSERT UPDATE P SET 语句插入的行被记录下来 UPDATE P SET 10522 31.00 7 UnitsInStock = (P.UnitsInStock – I.Quantity) I.Quantity) UnitsInStock2= (P.UnitsInStock –0.2 FROM Products AS P INNER JOIN Inserted AS P 10523 41 9 0.15 FROM Products AS9.65INNER JOIN Inserted AS II 3 触发器动作被执行 = I.ProductID ON P.ProductID I.ProductID ON P.ProductID = 30.00 10524 7 24 0.0
8
同步任务
同步任务参见实训教程p204页[同步任务1-1] [同步任务1-2] [同步任务1-3]
9
10.3 触发器综述
1.触发器概念 触发器是一类特殊的存储过程，被定义为在对特定表或 视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行. 2.触发器的特点：
它与表紧密相连，可以看作表定义的一部分； 它不能通过名称被直接调用，更不允许带参数，而是当用
10.4.2 INSERT 触发器
例2：在northwind数据库中的order details表上创建一个触发器,使得无论何 时向order details表中插入一条记录， 都将更新product表中的unitsInstock列， 即用原来的数量减去订购的数量。
17
10.4.2 INSERT 触发器
10.4.2 INSERT 触发器
INSERT 触发器的工作过程 – 在定义了 INSERT 触发器的表上执行 INSERT 语句 – INSERT 语句插入的行被记录下来 – 触发器动作被执行 inserted 表 – 触发 INSERT 触发器时，新行被同时增加到触发器表 和 inserted 表中 – inserted 表是保存了插入行的副本的逻辑表，它并不 实际存在于数据库中 – inserted 表允许用户引用 INSERT 语句所插入的数据， 这样触发器可以根据具体数据决定是否执行以及如何执 16 行特定语句

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

比较数据修改前后的状态 – 大部分触发器提供了引用被修改数据的能力，这样就允许用户在触 11 发器中引用正被修改语句所影响的行
10.3 触发器综述
4、 触发器的类型 DML触发器和DDL触发器。 DML触发器 DML触发器是当数据库服务器中发生数据操纵语言（DML） 事件，如INSERT、UPDATE、DELETE时所触发的操作。DML 触发器用于在数据被修改时强制执行业务规则。注意：SELECT语 句不能触发触发器操作，因为SELECT语句并没有修改表中的数 据。 DDL触发器 SQL SERVER 2005中新增了DDL触发器，该触发器是一种 特殊的触发器，当服务器或数据库中发生数据定义语句（DDL） 事件，如CREATE、ALTER、DROP数据库对象等操作时将调用 这些触发器。它们可以用于在数据库中执行管理任务，如审核以 及规范数据库操作。
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
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 语句触发
原子性（Atomic）
一致性（ConDemoltent） 独立性（Isolated）
持久性（Durable）
2
10.1 程序中的事务
3）事务类型 显式事务：显示事务是手工配置的事务。用保留字标识显 式事务的开始和结束。
开始显式事务，使用BEGIN TRAN。 结束显示事务，使用COMMIT TRAN。 取消事务，使用ROLL BACK TRAN命令。 隐式事务 ：在前一个事务完成时新事务隐式启动，但每个 事务仍以COMMIT或ROLLBACK语句显示完成。
户对表中的数据进行编辑时，自动执行；
它可以用于SQL
Server约束、默认值和规则的完整性检查， 实施更为复杂的数据完整性约束。
10
10.3 触发器综述


3、触发器的用途 在数据库中的相关表上实现级联更改 – 在数据库的相关表上使用触发器可实现级联更新或删除 强制比 CHECK 约束更复杂的数据完整性 – 和 CHECK 约束不同，触发器可以引用其他表中的列 – 通过下列方法使用触发器来强制复杂的引用完整性：根据情况确定 是否级联更新与删除、创建多行触发器、在数据库间强制引用完整 性 维护非标准数据
12
10.3 触发器综述
5、DML触发器的种类 两种类型：AFTER触发器和 INSTEAD OF触发器。 1）AFTER触发器 AFTER触发器又称为后触发器，该类触发器是在引 起触发器执行的修改语句成功完成之后执行。 2） INSTEAD OF触发器 INSTEAD OF触发器又称为替代触发器，当引起触 发器执行的修改语句停止执行时，该类触发器代替触发 操作执行。
Categories
CategoryID CategoryName Picture 1 在定义了 DELETE 触发器的表上 Description DELETE 语句删除的行被记录下来 DELETE Categories 1 Soft drinks, Deleted 执行 DELETE 语句 Beverages Products coffees… 0x15… WHERE Condiments ProductID Discontinued … categoryid Sweet and savory … 0x15… Dairy Products Cheeses 2 0x15… 4 CategoryID = 4 3 Confections Desserts, candies, … 0x15… 1 0 2 DELETE 语句删除的行被记录下来 2 4 Dairy Products Cheeses 0 0x15… 2 1 4 USE Northwind 3 0 CREATE TRIGGER Category_Delete 3 触发器动作被执行 4 0 ON Categories