《SQL触发器》由会员分享，可在线阅读，更多相关《SQL触发器（31页珍藏版）》请在金锄头文库上搜索。
1、触发器触发器vv一种特殊的存储过程一种特殊的存储过程vv可以对表实现复杂的数据完整性约束可以对表实现复杂的数据完整性约束回顾回顾:(三种数据完整性的实现方法三种数据完整性的实现方法)实体完整性体完整性primary key约束、约束、unique约束、标识列、惟一索引约束、标识列、惟一索引域完整性域完整性Check 约束、规则对象、约束、规则对象、 default 约束、默认值对象、约束、默认值对象、not null 约束约束参照完整性参照完整性foreign key约束约束提问提问:以上方法是否可以实现复杂的条件约束以上方法是否可以实现复杂的条件约束?如: 表中的某个字段,是否可以参考另一个
2、表的字段值?(Example:存货量存货量订购量订购量)库存表库存表采购表采购表本章教学内容1.触发器基本概念及优点2.创建和触发(激活)触发器3.触发器的修改4.触发器的删除学习目标认知目标：理解触发器的概念和作用了解触发器的触发方式了解inserted和deleted临时表能力目标:触发器的创建(重点)创建不同类型的触发器(难点)根据触发器的功能正确检验触发器的作用 (难点)12一、触发器基本概念v触发器的定义是一种表或视图执行insert、 delete、update操作时，被系统自动执行的特殊的存储过程。v创建触发器的目的对表实现复杂的数据完整性约束，以防止不正确的操作。 一、触发器基
3、本概念v使用触发器的优点:1)强制比CHECK约束更复杂的数据完整性2)使用自定义的错误信息3)实现数据库中多张表的级联更新或删除4)比较数据库修改前后数据的状态5)维护非规范化数据一、触发器基本概念v触发器的分类:1)按触发器激活的时机不同，分为：后触发器(重点)v只有执行了insert、 delete、update操作后,才激发的触发器。替代触发器(了解)v不执行命令所定义的insert、 delete、update操作,而仅执行触发器中定义的代码。2)按引起触发的事件不同，分为：Insert触发器Delete触发器Update触发器AFTER 触发器说明:v只能在表上创建只能在表上创建v
4、可针对表的同一个操作定义多个触发器可针对表的同一个操作定义多个触发器v可使用可使用SP_settriggerorder定义哪一个触定义哪一个触发器最先触发发器最先触发,哪一个触发器最后触发哪一个触发器最后触发返回INSTEAD OF触发器说明:v可在表、试图上定义可在表、试图上定义v同一操作只能定义一个同一操作只能定义一个Instead of触发器触发器返回一、触发器基本概念v每个触发器被激活时，系统都为它自动创建两个临时表：inserted表deleted表v临时表的特点：是逻辑表，由系统来维护，用户不能对它们进行修改。其结构与源表 (激活触发器的表)结构相同触发器执行完成后，该临时表会自动
5、被删除。一、触发器基本概念vdeleted 临时表的作用：存放执行delete或update语句后，要从源表中删除的行特点：源表和delete表不会有相同的行。vinserted临时表的作用：存放执行insert或update语句后，要在源表中插入的新行(新行同时插入了源表和inserted表中)特点：源表和inserted表有相同的行二、创建和激活触发器v创建格式：创建格式：CREATE TRIGGER owner.trigge_nameON owner.table|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS
6、IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesmentsv说明：说明：1个表上可有多个触发器。个表上可有多个触发器。 每个触发器只能作用在一个表上。每个触发器只能作用在一个表上。创建后触发器创建后触发器创建替代触发器击活触发器的事件类型指示触发器被触发后,将执行的数据库操作监测某列的值是否为更新操作二、创建和激活触发器 insert触发器vINSERT 触发器的工作过程在定义了 INSERT 触发器的表上执行INSERT 语句INSERT 语句插入的行被记录下来触发器动作被执行INSERT 触发器的工作过程（续）触发器的工
7、作过程（续）在定义了在定义了 INSERT 触发器的表上执行触发器的表上执行 INSERT 语句语句INSERT Order Details VALUES(10525, 2, 19.00, 5, 0.2)Order DetailsOrder DetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0 5 19.002 0.210523INSERT 语句插入的行被记录下来语句插入的行被记录下来insertedinserted10523219.0050.2触发器动作
8、被执行触发器动作被执行Order DetailsOrder DetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0 5 19.002 0.210523触发器代码：USE NorthwindCREATE TRIGGER OrdDet_InsertON Order DetailsFOR INSERTASUPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity)FROM Products AS P INNER J
9、OIN Inserted AS ION P.ProductID = I.ProductIDUPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDProductsProductsProductID UnitsInStock 123415106520215在定义了在定义了 INSERT INSERT 触发器的表上触发器的表上执行执行 INSERT INSERT 语句语句INSERT INSERT 语句插
10、入的行被记录下来语句插入的行被记录下来触发器动作被执行触发器动作被执行1 12 23 318.3.1 INSERT 18.3.1 INSERT 触发器的工作过程触发器的工作过程二、创建和激活触发器insert触发器例1：在grade表上创建名为tr_insert_gr的触发器，当向grade表进行插入操作时激发该触发器，并给出提示信息“新成绩插入!”vcreate trigger tr_insert_gr on grade after insert as print 新成绩插入! vgovinsert into grade values( 07003, C01, 89)二、创建和激活触发器in
11、sert触发器例2：v create trigger tr_insert_student on student after insertas declare 所在专业 char(20)select 所在专业=department from student, insertedwhere student.studentID = inserted. studentIDIF 所在专业 计算机 begin rollback transaction - 撤消插入操作 raiserror(不能插入非计算机专业的学生信息！,16 ,10) end二、创建和激活触发器delete触发器vDELETE 触发器的工
12、作过程在定义了 DELETE 触发器的表上执行 DELETE 语句DELETE 语句删除的行被记录下来触发器动作被执行DELETE 触发器的工作过程（续）触发器的工作过程（续）在定义了在定义了 DELETE 触发器的表上执行触发器的表上执行 DELETE 语句语句DeletedDeleted4Dairy Products Cheeses0x15DELETE 语句删除的行被记录下来语句删除的行被记录下来CategoriesCategoriesCategoryID123CategoryNameBeveragesCondimentsConfectionsDescriptionSoft drinks,
13、 coffeesSweet and savory Desserts, candies, Picture0x150x150x15 0x15CheesesDairy Products4DELETE CategoriesWHERE CategoryID = 4USE NorthwindCREATE TRIGGER Category_DeleteON CategoriesFOR DELETEASUPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDProd
14、uctsProductsProductID Discontinued 12340000触发器动作被执行触发器动作被执行2 1UPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryID在定义了在定义了 DELETE 触发器的表上触发器的表上执行执行 DELETE 语句语句DELETE 语句删除的行被记录下来语句删除的行被记录下来触发器动作被执行触发器动作被执行1 12 23 318.3.2 DELETE 18.3.2 DELETE 触发器的工作过程触发器
15、的工作过程二、创建和激活触发器delete触发器例1：要求student表中的信息不允许被删除,若执行删除操作,则给出错误提示。 v create trigger tr_delete_student on student after deleteasrollback transactionprint 该表信息不允许被删除!vgovdelete student where 学号= 07003二、创建和激活触发器update触发器vUPDATE 触发器的工作过程触发器的工作过程UPDATE 语句可以考虑为两个步骤：语句可以考虑为两个步骤：DELETE 步骤捕获数据的前像，步骤捕获数据的前像，INS
16、ERT 步步骤捕获数据的后像骤捕获数据的后像当在定义了触发器的表上执行当在定义了触发器的表上执行 UPDATE 语句语句的时候，原行（前像）被移到的时候，原行（前像）被移到 deleted 表中，表中，而更新的行（后像）则插入而更新的行（后像）则插入 inserted 表中表中触发器可以检索触发器可以检索 deleted 和和 inserted 表以及表以及被更新的表，来确定是否更新了多行以及如何被更新的表，来确定是否更新了多行以及如何执行触发器动作执行触发器动作UPDATE 触发器的工作过程（续）触发器的工作过程（续）在定义了在定义了 UPDATE 触发器的表上执行触发器的表上执行 UPDA
17、TE 语句语句UPDATE EmployeesSET EmployeeID = 17WHERE EmployeeID = 2UPDATE 语句被作为语句被作为 INSERT 和和 DELETE 语句记录语句记录EmployeesEmployeesEmployeeID LastName FirstNameTitleHireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSales Rep.RSales Rep.Sales Rep.2FullerAndrew Vice Pres.insertedinserted17FullerA
18、ndrew Vice Pres.deleteddeleted2FullerAndrew Vice Pres.触发器动作被执行触发器动作被执行USE NorthwindGOCREATE TRIGGER Employee_UpdateON EmployeesFOR UPDATEASIF UPDATE (EmployeeID)BEGIN TRANSACTIONRAISERROR (Transaction cannot be processed.* Employee ID number cannot be modified., 10, 1)ROLLBACK TRANSACTIONASIF UPDATE
19、 (EmployeeID)BEGIN TRANSACTIONRAISERROR (Transaction cannot be processed.* Employee ID number cannot be modified., 10, 1)ROLLBACK TRANSACTION 错误信息：事务无法进行！错误信息：事务无法进行！ * 成员号不能修改成员号不能修改EmployeesEmployeesEmployeeID LastName FirstNameTitleHireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSa
20、les Rep.RSales Rep.Sales Rep.2FullerAndrew Vice Pres.在定义了在定义了 UPDATE 触发器的表上触发器的表上执行执行 UPDATE 语句语句UPDATE 语句被作为语句被作为 INSERT 和和DELETE 语句记录语句记录触发器动作被执行触发器动作被执行1 12 23 318.3.3 UPDATE 18.3.3 UPDATE 触发器的工作过程触发器的工作过程二、创建和激活触发器3.update触发器例1:完成级联修改”学号”字段,即student表中的学号被修改,响应的grade表中的学号也要修改 create trigger tr_up
21、date_stduent on student after updateas declare 原学号 char(5)，新学号 char(5)select 原学号=deleted.studentID 新学号= inserted.studentIDfrom deleted, insertedupdate gradeset studentID=新学号 where studentID=原学号 二、创建和激活触发器update触发器v监视对特定列的更新语法：IF UPDATE ()允许触发器监测特定列，以对特定列的更新作出反应。例如发出不允许对列更新的错误信息，或者对新更新的列值进行处理二、创建和激活触
22、发器update触发器例2：在student表上创建名为tr_update_student的触发器，当的“姓名”列修改时激发该触发器，使用户不能修改“姓名”列。v create trigger tr_update_student on student after updateas if update(name)begin rollback transaction - 撤消修改操作 raiserror(不能修改学生姓名！ , 16 ,1) endvgovupdate xsqk set name=小花 where name=杨颖 二、创建和激活触发器instead of触发器vINSTEAD OF
23、 触发器可以在表和视图上定义 INSTEAD OF 触发器。INSTEAD OF 触发器代替原触发动作执行，增加了视图上所能进行的更新的种类每个表上对每个触发动作（INSERT、UPDATE 或 DELETE）只能定义一个 INSTEAD OF 触发器不能在具有 WITH CHECK OPTION 选项的视图上创建 INSTEAD OF 触发器INSTEAD OF 触发器的工作过程（续）触发器的工作过程（续）创建结合两个或多个表的视图创建结合两个或多个表的视图CREATE VIEW Customers ASSELECT * FROM CustomersMexUNIONSELECT * FROM
24、 CustomersGerCustomersMexCustomersMexCustomerID CompanyName CountryPhoneANATRANTONCENTCAna TrujillAntonio MCentro CoMexicoMexicoMexico(5) 555-4729(5) 555-3932(5) 555-3392CustomersGerCustomersGerCustomerID CompanyName CountryPhoneALFKIBLAUSDRACDAlfreds FuBlauer SeDrachenbGermanyGermanyGermany030-0074
25、3210621-084600241-039123INSTEAD OF 触发触发器将更新导向基表器将更新导向基表CustomersCustomersCustomerID CompanyName CountryPhoneALFKIANATRANTONAlfreds FuAna TrujillAntonio MGermanyMexicoMexico030-0074321(5) 555-4729(5) 555-3932最初对最初对 Customers 视图的插入不发生视图的插入不发生对视图执行对视图执行 UPDATE 操作操作ALFKI Alfreds Fu Germany 030-0074321AL
26、FKIAlfreds Fu Germany 030-0074321可以在表或视图上定义可以在表或视图上定义 INSTEAD OF 触发器触发器引发触发器的动作本身不发生作用引发触发器的动作本身不发生作用允许更新原先不可更新的视图允许更新原先不可更新的视图1 12 23 318.3.4 INSTEAD OF 触发器触发器的工作过程的工作过程三、更新、删除触发器v更新触发器的语法格式： alter trigger 触发器名v删除触发器的语法格式： drop trigger 触发器名,n四 查看触发器信息v与存储过程一样,触发器名称保存在系统表Sysobjects中,源代码保存在系统表Syscomm
27、ents中.v查看触发器信息:格式:sp_helptext v查看某表上存在的触发器信息格式:sp_helptrigger v使用系统表查看触发器的相关信息例:use pubs select name from sysobjects where type=tr go 推荐操作v只在需要的时候使用触发器v使触发器语句尽量简单v尽量减少触发器中ROLLBACK语句的使用本课小结u触发器的含义u触发器的类型按触发的时机不同：后触发器、替代触发器u触发器执行期间的临时表inserted 表 、deleted表u创建触发器insert触发器、delete触发器、update触发器u触发(激活)触发器(三种触发事件)u触发器的更新、删除
