数据库应用基础第八章触发器

1、1第八章 触发器复习1 如何执行存储过程？2 变量的定义方法。38.1 触发器概述8.2 创建触发器使用触发器8.4 修改和删除触发器主要内容：4触发器的基本概念 触发器概述 数据库触发器是一种在基表做UPDATE、INSERT或DELETE操作时被修自动执行的内嵌过程，用来防止对数据进行的不正确或不一致的修改 5 通过触发器可以把事务规则从应用程序代码移到数据库中从而确保事务规则被遵守，并能显著提高性能 ，保证安全，并实现过程完整性。6类型：DDL(Data Definition Language)数据定义语言触发器DML(Data Manipulation Language)数据操作语言触

2、发器7使用触发器的优点 1. 强制比CHCEK约束更复杂的数据完整性 在CHECK约束中不允许引用其他表中的列来完成检查工作，而触发器则可以引用其他表中的列来完成数据完整性的约束,如： 进出货.存货量定购量82. 使用自定义的错误信息 用户有时需要在数据完整性遭到破坏或其他情况下，发出预先自定义好的错误信息或动态自定义的错误信息93. 实现数据库中多张表的级联修改 在titleauthor、sales及roysched表中对各匹配行进行定位删除title_id列titles表在title_id列上定义一个删除触发器titleauthor表sales表roysched表此三列均有title_id

3、字段104. 比较数据库修改前后数据的状态 用户可在触发器中引用由于修改所影响的记录行 触发器提供访问由INSERT、UPDATE或DELETE语句引起的数据变化的前后状态115. 维护非规范化数据 非规范数据通常是指在表中的派生的、冗余的数据值维护非规范化数据应该通过使用触发器来实现表的级联是指不同表之间的主外键关系，维护表的级联可通过设置表的主键与外键的关系来实现注意区别128.2 创建触发器CREATE TRIGGER owner.trigge_nameON owner.table|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE在触发SQL语句

4、中指定的所有操作(引用级联操作和约束检查)成功完成后，执行此触发器 表示不执行其所定义的操作(INSERT、UPDATE、DELETE)而仅执行触发器本身激发触发器的定时时间13WITH ENCRYPTIONAS IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesments指定对标内某字段作增加或修改操作时触发器材起作用定义触发器被触发后，将执行的数据库操作 148.2.1 INSERT触发器 例：在pubs库的authors表上创建 my_trigger1触发器，该触发器被操作INSERT所触发USE pubsgoCREAT

5、E TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(unauthorized,10,1)15当向表authors插入数据时将触发触发器，但是数据仍能被插入表中INSERT INTO authorsvalues(172-33-1234,White,John,408496-7223,10932Bigge Rd.,Menlo Park,CA,94025, 1) 定义触发器时指定了FOR选项，AFTER为默认值，触发器只在INSERT中指定的操作都执行后才激发，因此仍能插入数据16Deleted表和 Inserted SQL Server为每个触发

6、器都创建了两个专用表：INSERTED表和DELETED表。这两个逻辑表，由系统来维护。用户不能对它们进行修改。它们存放在内存中而不是数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也被删除。17DELETED表存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE操作时，被删除的行从激活触发器的表中被移动到DELETED表，这两个表不会有共同的行。18INSERTED表存放由于执行INSERT或UPDATE语句而要向表中插入的所有行。在执行INSERT或UPDATE操作时，新的行同时添加到激活触

7、发器的表和INSERTD表中， INSERTD表的内容是激活触发器的表中新行的备份。19有没有什么办法能实现触发器被执行的同时，取消触发触发器的SQL语句的操作呢？用INSTEAD OF关键字来实现20例:在pubs库的authors表上创建触发器my_trigger2，它被操作DELETE所触发，且要求触发触发器的DELETE语句在执行后被取消USE PubsGOCREATE TRIGGER my_trigger2ON authorsINSTEAD OF DELETEASraiserror(No Right To Delete It,10,1)21如果在表authors中删除前面例子中新增的

8、记录，就会报错，如：DELETE from authorsWhere au_id=172-33-1234 228.2.2 UPDATE触发器 通过定义IF UPDATE 实现当特定列被更新时触发触发器不论该更新影响表中的多少行 若用户需要实现多个特定列中的任意一列被更新时触发触发器，可以通过在触发器定义中使用多个IF UPDATE 语句来实现 23例:在pubs库的authors表上建触发器member_trigger，它将被UPDATE操作激活,且不允许修改au_lname字段USE pubsgoCREATE TRIGGER member_triggerON authorsFOR UPDAT

9、EAS24不使用INSTEAD OF而是通过rollback transaction子句恢复原来的数据的方法来实现字段不被修改IF UPDATE(au_lname)BEGIN raiserror(Unauthorized!,10,1) rollback transactionEND25建好触发器后试着执行UPDATE操作USE pubsgoUPDATE authorsSET au_lname=WangtiWHERE au_lname=White运行结果显示：“Unauthorized!” 26在“查询分析器”中运行如下命令：USE pubsgoSELECT au_lnameFROM autho

10、rsWHERE au_lname LIKE W% 查询结果中只有两行的White的内容，可见刚才的更新操作并不能实现对表中au_lname字段的更新 27UPDATE可对未建立保护性触发的字段进行更新而不激发触发器，例如：USE pubsgoUPDATE authorsSET au_fname=WangtiWHERE au_fname=Johnson检索表authors可看到表内的信息被更新了，这是由于在au_fname字段上未建立UPDATE的触发返回消息：“所影响的行数为 1 行”288.2.3 DELETE 触发器 利用DELETE触发器，能在相应的表中实现当遇到删除动作时自动发出报警

11、29例：在pubs库的sales表上建立名为delete_trigger的DELETE触发器，实现对删除sales表中销售记录的操作给出报警，并取消当前的删除操作USE pubsgoCREATE TRIGGER delete_trigger ON salesFOR DELETEAS BEGIN raiserror(Unauthorized!,10,1) rollback transactionEND 308.2.4 查看触发器信息 象存储过程一样，触发器在创建后，系统把触发器的名称保存在系统表sysobjects中，并把创建的源代码保存在系统表syscomments中 311. 使用系统存储过

12、程 EXEC sp_helptext 例：使用sp_helptext 查看触发器delete_trigger的定义文本信息USE pubsEXEC sp_helptext delete_triggergo3233 可以通过指定WITH ENCRYPTION来对触发器的定义文本信息进行加密，加密后的触发器无法用sp_helptext查看相关信息34还可使用系统存储过程sp_helptrigger来查看某特定表上存在的触发器的某些信息EXEC sp_helptrigger 35例：用系统存储过程sp_helptrigger 查看表authors上存在的所有触发器的相关信息USE pubsEXEC

13、sp_helptrigger authorsgo36372. 使用系统表 例：用系统表sysobjects查看数据库pubs上的所有触发器的相关信息USE pubsSELECT name from sysobjectsWHERE type=TRgo388.3.1 使用触发器强制数据完整性约束和触发器都可以用来实施数据完整性，但两者各有优势触发器的可以包含使用 T-SQL 代码的复杂处理逻辑触发器可以支持约束的所有功能使用触发器39以下应用场合应考虑使用触发器： 除非REFERENCES子句定义了级联引用操作，否则FOREIGN KEY约束只能以与另一列中的值完全匹配的值来验证列值 应用程序要求

14、根据另一表中的列验证列值应用程序要求使用自定义信息和较为复杂的错误处理 408.3.2 使用触发器强制业务规则 触发器在强制数据完整性之外，还可强制实施对CHECK约束来说过于复杂的业务规则，包括对其他表中行的状态进行检查41例：在Northwind数据库中的Customers上建立DELETE触发器delete_customers，使得在删除表Customers中记录的同时，自动检查表Orders中是否有该客户的记录，如果存在该客户记录，则取消删除。42USE NorthwindgoCREATE TRIGGER delete_customersON dbo.CustomersFOR DELE

15、TEAS IF(SELECT COUNT(*) FROMOrders INNER JOIN DeletedON Orders.CustomerID= Deleted.CustomerID)043BEGINRAISERROR(You can not delete the customer with the order record.The transaction will be cancelled,10,1)ROLLBACK TRANSACTIONEND 448.4 修改和删除触发器 8.4.1 修改触发器 ALTER TRIGGER owner.trigge_nameON owner.tabl

16、e|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS45IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesments ALTER TRIGGER owner.trigge_nameON owner.table|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesments 46例：修改Northwind库中的