《数据库应用基础第八章触发器.ppt》由会员分享，可在线阅读，更多相关《数据库应用基础第八章触发器.ppt（49页珍藏版）》请在三一文库上搜索。
1、1,第八章 触发器,复习,1 如何执行存储过程？ 2 变量的定义方法。,3,8.1 触发器概述 8.2 创建触发器 8.3使用触发器 8.4 修改和删除触发器,主要内容：,4,8.1.1触发器的基本概念,8.1触发器概述,数据库触发器是一种在基表做UPDATE、INSERT或DELETE操作时被修自动执行的内嵌过程，用来防止对数据进行的不正确或不一致的修改,5,通过触发器可以把事务规则从应用程序代码移到数据库中从而确保事务规则被遵守，并能显著提高性能 ，保证安全，并实现过程完整性。,6,类型：,DDL(Data Definition Language)数据定义语言触发器 DML(Data Ma
2、nipulation Language)数据操作语言触发器,7,8.1.2使用触发器的优点,1. 强制比CHCEK约束更复杂的数据完整性 在CHECK约束中不允许引用其他表中的列来完成检查工作，而触发器则可以引用其他表中的列来完成数据完整性的约束,如：,进出货.存货量定购量,8,2. 使用自定义的错误信息,用户有时需要在数据完整性遭到破坏或其他情况下，发出预先自定义好的错误信息或动态自定义的错误信息,9,3. 实现数据库中多张表的级联修改,在titleauthor、sales及roysched表中对各匹配行进行定位删除,10,4. 比较数据库修改前后数据的状态,用户可在触发器中引用由于修改所影
3、响的记录行,11,5. 维护非规范化数据,非规范数据通常是指在表中的派生的、冗余的数据值,维护非规范化数据应该通过使用触发器来实现,12,8.2 创建触发器,CREATE TRIGGER owner.trigge_name ON owner.table|view FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE,在触发SQL语句中指定的所有操作(引用级联操作和约束检查)成功完成后，执行此触发器,表示不执行其所定义的操作(INSERT、UPDATE、DELETE)而仅执行触发器本身,激发触发器的定时时间,13,WITH ENCRYPTION AS IF UPDA
4、TE(column_name) and|or UPDATE(column_name) sql_statesments,指定对标内某字段作增加或修改操作时触发器材起作用,定义触发器被触发后，将执行的数据库操作,14,8.2.1 INSERT触发器,例：在pubs库的authors表上创建 my_trigger1触发器，该触发器被操作INSERT所触发 USE pubs go CREATE TRIGGER my_trigger1 ON authors FOR INSERT AS raiserror(unauthorized,10,1),15,当向表authors插入数据时将触发触发器，但是数据仍能
5、被插入表中 INSERT INTO authors values(172-33-1234,White,John,408496-7223,10932Bigge Rd.,Menlo Park,CA,94025, 1) 定义触发器时指定了FOR选项，AFTER为默认值，触发器只在INSERT中指定的操作都执行后才激发，因此仍能插入数据,16,Deleted表和 Inserted,SQL Server为每个触发器都创建了两个专用表：INSERTED表和DELETED表。这两个逻辑表，由系统来维护。用户不能对它们进行修改。它们存放在内存中而不是数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。
6、触发器执行完成后，与该触发器相关的这两个表也被删除。,17,DELETED表,存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE操作时，被删除的行从激活触发器的表中被移动到DELETED表，这两个表不会有共同的行。,18,INSERTED表,存放由于执行INSERT或UPDATE语句而要向表中插入的所有行。在执行INSERT或UPDATE操作时，新的行同时添加到激活触发器的表和INSERTD表中， INSERTD表的内容是激活触发器的表中新行的备份。,19,有没有什么办法能实现触发器被执行的同时，取消触发触发器的SQL语句的操作呢？,用INSTE
7、AD OF关键字来实现,20,例:在pubs库的authors表上创建触发器my_trigger2，它被操作DELETE所触发，且要求触发触发器的DELETE语句在执行后被取消 USE Pubs GO CREATE TRIGGER my_trigger2 ON authors INSTEAD OF DELETE AS raiserror(No Right To Delete It,10,1),21,如果在表authors中删除前面例子中新增的记录，就会报错，如： DELETE from authors Where au_id=172-33-1234,22,8.2.2 UPDATE触发器,通过定
8、义 IF UPDATE 实现当特定列被更新时触发触发器 不论该更新影响表中的多少行 若用户需要实现多个特定列中的任意一列被更新时触发触发器，可以通过在触发器定义中使用多个IF UPDATE 语句来实现,23,例:在pubs库的authors表上建触发器member_trigger，它将被UPDATE操作激活,且不允许修改au_lname字段 USE pubs go CREATE TRIGGER member_trigger ON authors FOR UPDATE AS,24,不使用INSTEAD OF而是通过rollback transaction子句恢复原来的数据的方法来实现字段不被修改
9、,IF UPDATE(au_lname) BEGIN raiserror(Unauthorized!,10,1) rollback transaction END,25,建好触发器后试着执行UPDATE操作 USE pubs go UPDATE authors SET au_lname=Wangti WHERE au_lname=White 运行结果显示：“Unauthorized!”,26,在“查询分析器”中运行如下命令： USE pubs go SELECT au_lname FROM authors WHERE au_lname LIKE W%,查询结果中只有两行的White的内容，可见
10、刚才的更新操作并不能实现对表中au_lname字段的更新,27,UPDATE可对未建立保护性触发的字段进行更新而不激发触发器，例如： USE pubs go UPDATE authors SET au_fname=Wangti WHERE au_fname=Johnson 检索表authors可看到表内的信息被更新了，这是由于在au_fname字段上未建立UPDATE的触发,返回消息：“所影响的行数为 1 行”,28,8.2.3 DELETE 触发器,利用DELETE触发器，能在相应的表中实现当遇到删除动作时自动发出报警,29,例：在pubs库的sales表上建立名为delete_trigge
11、r的DELETE触发器，实现对删除sales表中销售记录的操作给出报警，并取消当前的删除操作 USE pubs go CREATE TRIGGER delete_trigger ON sales FOR DELETE AS BEGIN raiserror(Unauthorized!,10,1) rollback transaction END,30,8.2.4 查看触发器信息,象存储过程一样，触发器在创建后，系统把触发器的名称保存在系统表sysobjects中，并把创建的源代码保存在系统表syscomments中,31,1. 使用系统存储过程,EXEC sp_helptext ,例：使用sp_
12、helptext 查看触发器delete_trigger的定义文本信息 USE pubs EXEC sp_helptext delete_trigger go,32,33,可以通过指定WITH ENCRYPTION来对触发器的定义文本信息进行加密，加密后的触发器无法用sp_helptext查看相关信息,34,还可使用系统存储过程sp_helptrigger来查看某特定表上存在的触发器的某些信息 EXEC sp_helptrigger ,35,例：用系统存储过程sp_helptrigger 查看表authors上存在的所有触发器的相关信息 USE pubs EXEC sp_helptrigger
13、 authors go,36,37,2. 使用系统表,例：用系统表sysobjects查看数据库pubs上的所有触发器的相关信息 USE pubs SELECT name from sysobjects WHERE type=TR go,38,8.3.1 使用触发器强制数据完整性,约束和触发器都可以用来实施数据完整性，但两者各有优势,8.3使用触发器,39,以下应用场合应考虑使用触发器：,除非REFERENCES子句定义了级联引用操作，否则FOREIGN KEY约束只能以与另一列中的值完全匹配的值来验证列值,应用程序要求根据另一表中的列验证列值,应用程序要求使用自定义信息和较为复杂的错误处理,
14、40,8.3.2 使用触发器强制业务规则,触发器在强制数据完整性之外，还可强制实施对CHECK约束来说过于复杂的业务规则，包括对其他表中行的状态进行检查,41,例：在Northwind数据库中的Customers上建立DELETE触发器delete_customers，使得在删除表Customers中记录的同时，自动检查表Orders中是否有该客户的记录，如果存在该客户记录，则取消删除。,42,USE Northwind go CREATE TRIGGER delete_customers ON dbo.Customers FOR DELETE AS IF (SELECT COUNT(*) F
15、ROM Orders INNER JOIN Deleted ON Orders.CustomerID= Deleted.CustomerID)0,43,BEGIN RAISERROR(You can not delete the customer with the order record. The transaction will be cancelled,10,1) ROLLBACK TRANSACTION END,44,8.4 修改和删除触发器,8.4.1 修改触发器,ALTER TRIGGER owner.trigge_name ON owner.table|view FOR|AFTE
16、R|INSTEAD OF INSERT,UPDATE,DELETE WITH ENCRYPTION AS,45,IF UPDATE(column_name) and|or UPDATE(column_name) sql_statesments ALTER TRIGGER owner.trigge_name ON owner.table|view FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE WITH ENCRYPTION AS IF UPDATE(column_name) and|or UPDATE(column_name) sql_statesments
17、,46,例：修改Northwind库中的视图Customers上的INSTEAD OF触发器delete_customers，使得用户在该视图上执行除删除外的增加，修改操作时，也自动给出错误提示信息并撤销此次操作,47,USE Northwind go ALTER TRIGGER delete_customers ON Customers INSTEAD OF DELETE,INSERT,UPDATE AS RAISERROR(You can not insert,delete or update records from this view,10,1),48,8.4.2 删除触发器,DROP TRIGGER owner.trigge_name,49,8.4.3 禁止或启用触发器,当一个触发器被禁止后，它仍然存在于触发器表上，只是触发器的动作不再执行，直到该它被重新启用。启用/禁止命令如下： ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL|trigger_name,n ,