SQL-07存储过程与触发器.ppt
第7章 存储过程与触发器,本章内容：存储过程触发器 触发器类型 创建触发器 inserted和deleted临时表,7.2 触发器,触发器是一种特殊类型的存储过程，当在指定的表中使用INSERT、DELETE或UPDATE语句修改表中的数据时，SQL Server自动执行触发器,以响应INSERT、UPDATE或DELETE语句。触发器与数据修改事件相关联，可分为三类：INSERT触发器：当向表中插入数据行时自动触发 DELETE触发器：当从表中删除数据行时自动触发 UPDATE触发器：当修改表中数据时自动触发触发器常用来实现复杂的数据完整性。,7.2 触发器,例：XS和XS_KC之间的引用完整性、学分和总学分之间的关系。,XS,XS_KC,7.2.1 触发器的创建,企业管理器创建触发器1、选择表，右击鼠标，选择“所有任务”,单击“管理触发器”。2、在“触发器属性”对话框中创建触发器。,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 触发器。,1、T-SQL创建触发器,INSTEAD OF：指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。DELETE,INSERT,UPDATE：指定触发器的类型，即指定在表或视图上执行哪些数据修改语句时将激活触发器。sql_statement：定义触发器被触发后，将执行的操作。例1：插入触发器CREATE TRIGGER insert_triggerON xs_kc for INSERTAS SELECT insert into xs_kc表GO,1、T-SQL创建触发器,执行：insert xs_kc values(001101,106,80,3)可看出：SELECT insert into xs_kc表 已经执行例2：插入触发器和更新触发器CREATE TRIGGER update_triggerON xs_kcfor INSERT,UPDATEAS SELECT update on xs_kcGOinsert into xs_kc values(010102,206,80,3)update xs_kc set 成绩=90 where 学号=010101,2、触发器中的两个特殊表,执行触发器时，系统自动创建两个特殊的临时表：inserted表：表结构同触发器表，用于存储INSERT和UPDATE 语句所影响的行的副本。deleted表：表结构同触发器表，用于存储DELETE和UPDATE语句所影响的行的副本。临时表中的数据由系统自动维护：执行INSERT时，新建行被同时添加到inserted表和触发器表中。执行DELETE时，行从触发器表中删除，并传输到deleted表中。执行UPDATE时，从触发器表中删除旧行，并传输到deleted表中，新建行被添加到inserted表。只能在触发器中访问临时表中的数据，且不能修改其内容。,3、应用举例,例7.12 创建一触发器，当向xs_kc表中插入一行数据时，检查插入数据中的学号是否在xs表中存在,课程号是否在kc表中存在：CREATE TRIGGER check_trig ON xs_kc FOR INSERTASIF EXISTS(SELECT*FROM inserted a WHERE a.学号 NOT IN(SELECT b.学号 FROM xs b)or a.课程号 NOT IN(SELECT c.课程号 FROM kc c)BEGINRAISERROR(违背数据的一致性,16,1)ROLLBACK TRANSACTION END 执行：insert xs_kc values(110100,106,80,2)修改上触发器，完成 xs.总学分=xs.总学分+xs_kc.学分,3、应用举例,例：库存表存放当前仓库中现有产品的情况，出库表存放已经出库的产品的情况，现需出15台电视机。,步骤：1、INSERT 出库表 VALUES(02，2006.3.10,15)2、UPDATE 库存表 SET 库存数量=库存数量 15 WHERE 产品号=02,出库表,库存表,3、应用举例,CREATE TRIGGER TR_出库表 ON 出库表 AFTER INSERTAS 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)可完成两个任务。,4、INSTEAD OF 触发器,触发器中定义的操作：AFTER 触发器：在操作语句的功能完成之后执行。INSTEAD OF触发器：替代操作语句的执行,例：有如下表:,XYMC,XMC,定义视图：CREATE VIEW VIEW_MCAS SELECT XYMC.*,系代码,系名称 FROM XYMC INNER JOIN XMC ON XYMC.学院代码=XMC.学院代码试定义视图的插入操作,4、INSTEAD OF 触发器,XYMC,XMC,VIEW_MC,INSERT VIEW_MC VALUES(03,信息学院,0301,电子工程),4、INSTEAD OF 触发器,INSERT触发器定义为:CREATE TRIGGER TR_VIEW_MC ON VIEW_MC INSTEAD OF INSERTAS 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),5、对特定列的更改进行测试,触发器中可以使用 IF UPDATE子句或IF COLUMNS_UPDATED()子句检查表中的列是否被更新。格式：CREATE TRIGGER trigger_name ON table|view FOR|AFTER|INSTEAD OF INSERT,UPDATEAS IF UPDATE(column)AND|OR UPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask)comparison_operator column_bitmask.n sql_statement.n,5、对特定列的更改进行测试,IF UPDATE(列名)子句:用于检查INSERT 或 UPDATE 语句是否影响到表中的一个特定列。无论何时，当列被赋值时，该子句即为 TRUE。IF COLUMNS_UPDATED()子句：用于检查表中已用 INSERT 或 UPDATE 语句更新的列。该子句返回二进制位数据，其值表示插入或更新了表中的哪些列。从右边开始，每一位对应表中一列:若对应位为0，表示没有插入或更新。若对应位为1，表示此列有插入或修改。例:xs_kc表COLUMNS_UPDATED()返回一个字节,低4位代表4列的状态,最低位为学号的状态。例如0 x04,表示成绩发生了变化。,5、对特定列的更改进行测试,例如：判别xs_kc表中的学号或课程号列是否被修改CREATE TRIGGER my_trig1 ON xs_kc FOR UPDATEAS IF(UPDATE(学号)or UPDATE(课程号)BEGINRAISERROR(违背数据的一致性,16,1)ROLLBACK TRANSACTION END或：CREATE TRIGGER my_trig2 ON xs_kc FOR UPDATEAS IF(COLUMNS_UPDATED()&3 0)BEGINRAISERROR(违背数据的一致性,16,1)ROLLBACK TRANSACTION END,触发器的管理,7.2.3 触发器的修改 企业管理器 ALTER TRIGGER7.2.4 触发器的删除 企业管理器 DROP TRIGGER trigger_name,n获得有关触发器的信息sp_helptrigger 表名 sp_helptext 触发器名,表设计举例,例：现有如下逻辑结构的表：库存表和出库表，库存表存放当前仓库中现有产品的情况，出库表存放已经出库的产品的情况，,要求：库存表中每行数据若未被引用可直接修改、删除；若被引用则不可删除，库存数量不可直接修改，每次出库在出库表登记一条出库信息，并相应减少库存数量(通过入库增加库存数量，此处不考虑），产品号可修改，其它项目不限制。请在数据库中实现两表，要求数据完整性利用服务器机制实现。,出库表,库存表,作 业,1、了解触发器的触发机制。2、掌握使用inserted和deleted临时表。3、熟练使用T-SQL语句创建触发器。4、给X公司数据库中创建的表定义触发器，以实现数据完整性，例如：产品表 定货单表 销售单表,
