1 / 20

第 7 章 存储过程与触发器

第 7 章 存储过程与触发器. 本章内容： 存储过程 触发器 触发器类型 创建触发器 inserted 和 deleted 临时表. 7.2 触发器. 触发器是一种 特殊类型的存储过程 ，当在指定的表中使用 INSERT 、 DELETE 或 UPDATE 语句 修改表 中的数据时， SQL Server 自动执行触发器 , 以 响应 INSERT 、 UPDATE 或 DELETE 语句。 触发器与数据修改事件相关联，可分为三类： INSERT 触发器：当向表中插入数据行时自动触发 DELETE 触发器：当从表中删除数据行时自动触发

leal
Télécharger la présentation

第 7 章 存储过程与触发器

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第7章 存储过程与触发器 本章内容： 存储过程 触发器 触发器类型 创建触发器 inserted和deleted临时表

  2. 7.2 触发器 • 触发器是一种特殊类型的存储过程，当在指定的表中使用INSERT、DELETE或UPDATE语句修改表中的数据时，SQL Server自动执行触发器,以响应INSERT、UPDATE或DELETE语句。 • 触发器与数据修改事件相关联，可分为三类： • INSERT触发器：当向表中插入数据行时自动触发 • DELETE触发器：当从表中删除数据行时自动触发 • UPDATE触发器：当修改表中数据时自动触发 • 触发器常用来实现复杂的数据完整性。

  3. 7.2 触发器 例：XS和XS_KC之间的引用完整性、学分和总学分之间的关系。 XS XS_KC

  4. 7.2.1 触发器的创建 • 企业管理器创建触发器 • 1、选择表，右击鼠标，选择“所有任务”,单击“管理触发器”。 • 2、在“触发器属性”对话框中创建触发器。

  5. 1、T-SQL创建触发器 • T-SQL语句创建触发器 • CREATE TRIGGER trigger_name • ON { table | view } • { { FOR|AFTER|INSTEAD OF} • {[INSERT][,][DELETE][,] [UPDATE]} • AS • sql_statement [ ...n ] • 说明： • trigger_name：是触发器的名称。在数据库中必须唯一。 • Table | view：是在其上执行触发器的表(称为触发器表)或视图。 • AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。不能在视图上定义 AFTER 触发器。

  6. 1、T-SQL创建触发器 INSTEAD OF：指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。 { [DELETE] [,] [INSERT] [,] [UPDATE] }：指定触发器的类型，即指定在表或视图上执行哪些数据修改语句时将激活触发器。 sql_statement：定义触发器被触发后，将执行的操作。 例1：插入触发器 CREATE TRIGGER insert_trigger ON xs_kc for INSERT AS SELECT ‘insert into xs_kc表' GO

  7. 1、T-SQL创建触发器 执行：insert xs_kc values('001101','106',80,3) 可看出：SELECT ‘insert into xs_kc表’已经执行 例2：插入触发器和更新触发器 CREATE TRIGGER update_trigger ON xs_kc for INSERT,UPDATE AS SELECT 'update on xs_kc' GO insert into xs_kc values('010102','206',80,3) update xs_kc set 成绩=90 where 学号='010101'

  8. 2、触发器中的两个特殊表 • 执行触发器时，系统自动创建两个特殊的临时表： • inserted表：表结构同触发器表，用于存储INSERT和UPDATE 语句所影响的行的副本。 • deleted表：表结构同触发器表，用于存储DELETE和UPDATE语句所影响的行的副本。 • 临时表中的数据由系统自动维护： • 执行INSERT时，新建行被同时添加到inserted表和触发器表中。 • 执行DELETE时，行从触发器表中删除，并传输到deleted表中。 • 执行UPDATE时，从触发器表中删除旧行，并传输到deleted表中，新建行被添加到inserted表。 • 只能在触发器中访问临时表中的数据，且不能修改其内容。

  9. 3、应用举例 例7.12 创建一触发器，当向xs_kc表中插入一行数据时，检查插入数据中的学号是否在xs表中存在,课程号是否在kc表中存在： CREATE TRIGGER check_trig ON xs_kc FOR INSERT AS IF EXISTS (SELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT b.学号 FROM xs b) or a.课程号 NOT IN (SELECT c.课程号 FROM kc c)) BEGIN RAISERROR('违背数据的一致性',16,1) ROLLBACK TRANSACTION END 执行：insert xs_kc values('110100','106',80,2) 修改上触发器，完成 xs.总学分 = xs.总学分 + xs_kc.学分

  10. 3、应用举例 例：库存表存放当前仓库中现有产品的情况， 出库表存放已经出库的产品的情况， 现需出15台电视机。 库存表 出库表 ② ① 步骤：1、INSERT 出库表 VALUES(’02’，’2006.3.10’,15) 2、UPDATE 库存表 SET 库存数量 =库存数量 – 15 WHERE 产品号 = ’02‘

  11. 3、应用举例 CREATE TRIGGER TR_出库表 ON 出库表 AFTER INSERT AS DECLARE @ID char(6), @num int DECLARE @tmp int SELECT @ID = 产品号, @num = 出库数量 FROM inserted SELECT @tmp = 库存数量 FROM 库存表 WHERE 产品号 = @ID IF @tmp - @num >= 0 BEGIN UPDATE 库存表 SET 库存数量 = 库存数量 - @num WHERE 产品号 = @ID COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN RAISERROR('库存数量不够!',16,1) END 执行：Insert 出库表 VALUES(‘02’,‘2006-3-10’,1) 可完成两个任务。

  12. 4、INSTEAD OF 触发器 • 触发器中定义的操作： • AFTER 触发器：在操作语句的功能完成之后执行。 • INSTEAD OF触发器：替代操作语句的执行 例：有如下表: XYMC XMC 定义视图： CREATE VIEW VIEW_MC AS SELECT XYMC.*, 系代码,系名称 FROM XYMC INNER JOIN XMC ON XYMC.学院代码=XMC.学院代码 试定义视图的插入操作

  13. 4、INSTEAD OF 触发器 XYMC XMC VIEW_MC INSERT VIEW_MC VALUES(‘03’,’信息学院’,’0301’,’电子工程’)

  14. 4、INSTEAD OF 触发器 INSERT触发器定义为: CREATE TRIGGER TR_VIEW_MC ON VIEW_MC INSTEAD OF INSERT AS DECLARE @xy_id char(2),@xy_name char(10) DECLARE @x_id char(4),@x_name char(10) SELECT @xy_id =学院代码,@xy_name=学院名称,@x_id=系代码, @x_name=系名称 FROM inserted IF NOT EXISTS(SELECT * FROM XYMC WHERE 学院代码 = @xy_id) INSERT XYMC VALUES(@xy_id,@xy_name) IF NOT EXISTS(SELECT * FROM XMC WHERE 系代码 = @x_id) INSERT XMC VALUES(@x_id,@x_name,@xy_id)

  15. 5、对特定列的更改进行测试 触发器中可以使用 IF UPDATE子句或IF COLUMNS_UPDATED() 子句检查表中的列是否被更新。 格式： CREATE TRIGGER trigger_name ON { table | view } { { FOR|AFTER|INSTEAD OF} {[INSERT][,] [UPDATE]} AS [ { IF UPDATE(column )[ { AND | OR } UPDATE(column ) ][ ...n ] | IF ( COLUMNS_UPDATED(){ bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ]} ] sql_statement [ ...n ]

  16. 5、对特定列的更改进行测试 • IF UPDATE (列名)子句: 用于检查INSERT 或 UPDATE 语句是否影响到表中的一个特定列。无论何时，当列被赋值时，该子句即为 TRUE。 • IF COLUMNS_UPDATED()子句：用于检查表中已用 INSERT 或 UPDATE 语句更新的列。该子句返回二进制位数据，其值表示插入或更新了表中的哪些列。从右边开始，每一位对应表中一列: • 若对应位为0，表示没有插入或更新。 • 若对应位为1，表示此列有插入或修改。 • 例: xs_kc表 • COLUMNS_UPDATED()返回一个字节, 低4位代表4列的状态, 最低位为学号的状态。 例如0x04,表示成绩发生了变化。

  17. 5、对特定列的更改进行测试 例如：判别xs_kc表中的学号或课程号列是否被修改 CREATE TRIGGER my_trig1 ON xs_kc FOR UPDATE AS IF (UPDATE(学号) or UPDATE(课程号) ) BEGIN RAISERROR('违背数据的一致性',16,1) ROLLBACK TRANSACTION END 或： CREATE TRIGGER my_trig2 ON xs_kc FOR UPDATE AS IF ( COLUMNS_UPDATED() & 3 > 0 ) BEGIN RAISERROR('违背数据的一致性',16,1) ROLLBACK TRANSACTION END

  18. 触发器的管理 • 7.2.3 触发器的修改 • 企业管理器 • ALTER TRIGGER • 7.2.4 触发器的删除 • 企业管理器 • DROP TRIGGER {trigger_name}[,…n] • 获得有关触发器的信息 • sp_helptrigger表名 • sp_helptext触发器名

  19. 表设计举例 例：现有如下逻辑结构的表：库存表和出库表，库存表存放当前仓库中现有产品的情况，出库表存放已经出库的产品的情况， 库存表 出库表 要求：库存表中每行数据若未被引用可直接修改、删除；若被引用则不可删除，库存数量不可直接修改，每次出库在出库表登记一条出库信息，并相应减少库存数量(通过入库增加库存数量，此处不考虑），产品号可修改，其它项目不限制。 请在数据库中实现两表，要求数据完整性利用服务器机制实现。

  20. 作 业 • 1、了解触发器的触发机制。 • 2、掌握使用inserted和deleted临时表。 • 3、熟练使用T-SQL语句创建触发器。 • 4、给X公司数据库中创建的表定义触发器，以实现数据完整性，例如： • 产品表 • 定货单表 • 销售单表 • …

More Related