数据库原理与应用06.ppt

1、1/51,第十章 触发器及其应用,2/51,10.1.1触发器的基本概念,10.1触发器的基本概念和优点,数据库触发器是一种在基表被修改时自动执行的内嵌过程，当使用UPDATE、INSERT或DELETE命令在指定表中对数据进行修改时，用来防止对数据进行的不正确或不一致的修改,3/51,通过触发器可以把事务规则从应用程序代码移到数据库中从而确保事务规则被遵守，并能显著提高性能,4/51,10.1.2使用触发器的优点,1. 强制比CHCEK约束更复杂的数据完整性 在CHECK约束中不允许引用其他表中的列来完成检查工作，而触发器则可以引用其他表中的列来完成数据完整性的约束,进出货：存货量定购量,5

2、/51,2. 使用自定义的错误信息,用户有时需要在数据完整性遭到破坏或其他情况下，发出预先自定义好的错误信息或动态自定义的错误信息,6/51,3. 实现数据库中多张表的级联修改,在titleauthor、sales及roysched表中对各匹配行进行定位删除,7/51,4. 比较数据库修改前后数据的状态,用户可在触发器中引用由于修改所影响的记录行,8/51,5. 维护非规范化数据,非规范数据通常是指在表中的派生的、冗余的数据值,维护非规范化数据应该通过使用触发器来实现,9/51,10.2 触发器的创建,CREATE TRIGGER owner.trigge_name ON owner.tabl

3、e|view FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE,在触发SQL语句中指定的操作、引用级联操作和约束检查成功完成后，执行此触发器,指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作,10/51,WITH ENCRYPTION AS IF UPDATE(column_name) and|or UPDATE(column_name) sql_statesments,指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作,定义触发器被触发后，将执行的数据库操作,11/51,10.2.1 INSERT触发器,例：在pubs库的autho

4、rs表上创建 my_trigger1触发器，该触发器被操作INSERT所触发 USE pubs go CREATE TRIGGER my_trigger1 ON authors FOR INSERT AS raiserror(unauthorized,10,1),12/51,当向表authors插入数据时将触发触发器，但是数据仍能被插入表中 INSERT INTO authors values(172-33-1234,White,John,408496-7223,10932Bigge Rd.,Menlo Park,CA,94025, 1) 定义触发器时指定了FOR选项，AFTER为默认值，触发

5、器只在INSERT中指定的操作都执行后才激发，因此仍能插入数据,13/51,有没有什么办法能实现触发器被执行的同时，取消触发触发器的SQL语句的操作呢？,用INSTEAD OF关键字来实现,14/51,例:在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),15/51,如果在表author

6、s中删除前面例子中新增的记录，就会报错，如： DELETE * from authors Where au_id=172-33-1234,16/51,10.2.2 UPDATE触发器,通过定义 IF UPDATE 实现当特定列被更新时触发触发器 不论该更新影响表中的多少行 若用户需要实现多个特定列中的任意一列被更新时触发触发器，可以通过在触发器定义中使用多个IF UPDATE 语句来实现,17/51,例:在pubs库的authors表上建触发器member_trigger，它将被UPDATE操作激活,且不允许修改au_lname字段 USE pubs go CREATE TRIGGER mem

7、ber_trigger ON authors FOR UPDATE AS,18/51,不使用INSTEAD OF而是通过rollback transaction子句恢复原来的数据的方法来实现字段不被修改,IF UPDATE(au_lname) BEGIN raiserror(Unauthorized!,10,1) rollback transaction END,19/51,建好触发器后试着执行UPDATE操作 USE pubs go UPDATE authors SET au_lname=Wangti WHERE au_lname=White 运行结果显示：“Unauthorized!”,2

8、0/51,在“查询分析器”中运行如下命令： USE pubs go SELECT au_lname FROM authors WHERE au_lname LIKE W%,查询结果中只有两行的White的内容，可见刚才的更新操作并不能实现对表中au_lname字段的更新,21/51,UPDATE可对未建立保护性触发的字段进行更新而不激发触发器，例如： USE pubs go UPDATE authors SET au_fname=Wangti WHERE au_fname=Johnson 检索表authors可看到表内的信息被更新了，这是由于在au_fname字段上未建立UPDATE的触发,返

9、回消息：“所影响的行数为 1 行”,22/51,10.2.3 DELETE 触发器,利用DELETE触发器，能在相应的表中实现当遇到删除动作时自动发出报警,23/51,例：在pubs库的sales表上建立名为delete_trigger的DELETE触发器，实现对删除sales表中销售记录的操作给出报警，并取消当前的删除操作 USE pubs go CREATE TRIGGER delete_trigger ON sales FOR DELETE AS BEGIN raiserror(Unauthorized!,10,1) rollback transaction END,24/51,10.2

10、.4 查看触发器信息,象存储过程一样，触发器在创建后，系统把触发器的名称保存在系统表sysobjects中，并把创建的源代码保存在系统表syscomments中,25/51,1. 使用系统存储过程,EXEC sp_helptext ,例：使用sp_helptext 查看触发器delete_trigger的定义文本信息 USE pubs EXEC sp_helptext delete_trigger go,26/51,27/51,可以通过指定WITH ENCRYPTION来对触发器的定义文本信息进行加密，加密后的触发器无法用sp_helptext查看相关信息,28/51,还可使用系统存储过程sp

11、_helptrigger来查看某特定表上存在的触发器的某些信息 EXEC sp_helptrigger ,29/51,例：用系统存储过程sp_helptrigger 查看表authors上存在的所有触发器的相关信息 USE pubs EXEC sp_helptrigger authors go,30/51,31/51,2. 使用系统表,例：用系统表sysobjects查看数据库pubs上的所有触发器的相关信息 USE pubs SELECT name from sysobjects WHERE type=TR go,32/51,3. 用“企业管理器” 查看触发器,33/51,10.3.1 使用

12、触发器强制数据完整性,约束和触发器都可以用来实施数据完整性，但两者各有优势,34/51,实体完整性应在最低级别上通过索引进行强制，这些索引或是 PRIMARY KEY和UNIQUE约束的一部分，或是在约束之外独立创建的,域完整性应通过CHECK约束来强制,引用完整性应通过FOREIGN KEY 约束来强制,35/51,以下应用场合应考虑使用触发器：,除非REFERENCES子句定义了级联引用操作，否则FOREIGN KEY约束只能以与另一列中的值完全匹配的值来验证列值,应用程序要求根据另一表中的列验证列值,应用程序要求使用自定义信息和较为复杂的错误处理,36/51,10.3.2 使用触发器强制

13、业务规则,触发器在强制数据完整性之外，还可强制实施对CHECK约束来说过于复杂的业务规则，包括对其他表中行的状态进行检查,37/51,例：在Northwind数据库中的Customers上建立DELETE触发器delete_customers，使得在删除表Customers中记录的同时，自动检查表Orders中是否有该客户的记录，如果存在该客户记录，则取消删除。,38/51,USE Northwind go CREATE TRIGGER delete_customers ON dbo.Customers FOR DELETE AS IF (SELECT COUNT(*) FROM Orders

14、 INNER JOIN Deleted ON Orders.CustomerID= Deleted.CustomerID)0,39/51,BEGIN RAISERROR(You can not delete the customer with the order record. The transaction will be cancelled,10,1) ROLLBACK TRANSACTION END,40/51,10.4 修改和删除触发器,10.4.1 修改触发器,ALTER TRIGGER owner.trigge_name ON owner.table|view FOR|AFTER|

15、INSTEAD OF INSERT,UPDATE,DELETE WITH ENCRYPTION AS,41/51,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_statesment

16、s,42/51,例：修改Northwind库中的视图Customers上的INSTEAD OF触发器delete_customers，使得用户在该视图上执行除删除外的增加，修改操作时，也自动给出错误提示信息并撤销此次操作,43/51,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),44/51,10.4.2

17、 删除触发器,DROP TRIGGER owner.trigge_name,45/51,10.4.3 禁止或启用触发器,当一个触发器被禁止后，它仍然存在于触发器表上，只是触发器的动作不再执行，直到该它被重新启用。启用/禁止命令如下： ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL|trigger_name,n ,46/51,10.5 嵌套触发器和递归触发器,10.5.1 嵌套触发器,若一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，所有触发器依次触发,嵌套 触发器,最深可嵌套 32 层,47/51,使用系统存

18、储过程sp_config设置触发器是否禁止嵌套： EXEC sp_config inested_TRIGGER , 0 | 1,允许嵌套,禁止嵌套,若在嵌套触发器的任意层中出错，则整个事务都将取消,且回滚,48/51,用“企业管理器”来设置嵌套允许与否,49/51,10.5.2 递归触发器,当修改表中数据的触发器激活了第二个触发器，第二个触发器又通过修改原始表中的数据激活了激活它的触发器，从而形成递归触发器,触发器,触发器,50/51,递归触发器的递归方式,直接递归,间接递归,触发器激发并执行一个操作，而该操作又使同一触发器再次激发,触发器激发并执行一个操作，该操作又使另一表中的某触发器激发，

19、该触发器使原始表得到更新，从而再次引发第一个触发器,51/51,第十一章 用户自定义函数,52/51,11.1 用户自定义函数的基本概念,SQL Server 2000 允许创建用户定义函数用户定义函数是可返回值的例程,53/51,用户定义函数种类,返回可更新数据表的函数,返回不可更新数据表的函数,返回标量值的函数,若函数含单个SELECT语句且可更新，则返回的数据表可更新,若函数含多个SELECT语句或一个不可更新的SELECT语句，则返回的数据表不可更新,54/51,11.2 创建用户自定义函数,11.2.1创建用户自定义函数,CREATE FUNCTION ower-name, func

20、tion-name (parameter-name scalar_parameter_datetype =default,n),参数名，局部变量,参数数据类型,55/51,RETURNS scalar_return_datatype WITH ,n AS BEGIN sql-statement RETURN scalar-expression END,用户自定义函数的返回值,函数中返回值的表达式,56/51,SCHEMABINING选项可将函数绑定到它所引用的数据库对象,function-option,ENCRYPTION选项可以实现 SQL Server加密包含CREATE FUNCTION

21、语句文本的系统表列,57/51,例：在Northwind库上创建自定义函数my_function1，通过向该函数输入代表商品年销售额的money类型参数的大小返回字符串，若年销售额大于10000，返回“热销商品”，否则返回“非热销商品”,58/51,USE Northwind go CREATE FUNCTION my_function1 (moneyinput money) RETURNS nvarchar(5),59/51,BEGIN DECLARE returnstring nvarchar(5) IF moneyinput10000 SET returnstring=非热销商品 ELS

22、E SET returnstring=热销商品 RETURN returnstring END,60/51,在Northwind库上有一个记录各种 产品在1997年销售额的视图，通 过它来引用新建的my_function1， 查看哪些商品属于热销商品 USE Northwind go SELECT ProductName, ProductSales, dbo.my_function1(ProductSales) AS Discription FROM Product sales for 1997 WHERE CategoryName=Dairy Products,61/51,62/51,11.

23、2.2查看用户自定义函数,自定义函数的名称保存在sysobjects系统表中,创建自定义函数的源代码保存在syscomments系统表中,63/51,1. 使用系统存储过程查看,EXEC sp_help(sp_helptext) ,例：用系统存储过程sp_helptext 查看用户自定义函数my_funciton1的定义文本信息 USE Northwind go EXEC sp_helptext my_function1 go,64/51,65/51,2. 使用系统表或系统信息架构视图,在SQL Server中存在三个信息架构视图报告有关用户自定义函数的信息,ROUTINES PARAMETE

24、RS ROUTINE_COLUMNS,这些信息架构视图也是基于系统表sysobjects和syscomments实现的,66/51,例：使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。 USE Northwind SELECT * from sysobjects WHERE type=FN go,67/51,11.3 用户自定义函数类型,自定义函数有三种类型,标量函数,内嵌表值函数,多语句表值函数,68/51,11.3.1 标量函数,标量函数返回在 RETURNS子句中定义的数据类型的单个数据值,标量函数可重复调用,69/51,例：创建标量函数，

25、要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为 : ，并允许用户自行定义分隔符,70/51,CREATE FUNCTION my_function2 (DATE datetime, separator nvarchar(2)=:) RETURNS nvarchar(20) BEGIN DECLARE returnstring nvarchar(20),71/51,SET returnstring=今天是 +CONVERT(nvarchar(5), datepart(year,date)+年 +separator +CONVERT(nvarchar(5), datepart(

26、month,date)+月 +separator +CONVERT(nvarchar(5), datepart(day,date)+日 RETURN returnstring END,72/51,如何调用新建立的标量函数呢？ 可以运行如下命令： SELECT dbo.my_function2(GETDATE(),-),返回当天的年、月和日,73/51,11.3.2 内嵌表值函数,在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值函数所返回的表,可替代视图，可用在T-SQL查询中允许表或视图表达式的地方,74/51,视图：受限于单个 SELECT 语

27、句，不允许包含用户自己提供的参数,内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强,返回表的内嵌表值函数还可替换返回单个结果集的存储过程,75/51,例: 在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函数my_function3,76/51,USE Northwind Go CREATE FUNCTION my_function3 (RegionParameter nvarchar(25) RETURNS table AS,77/51,RETURN (SELECT City, CompanyName, ContactNa

28、me, Customers AS Relationship FROM Customers WHERE City=RegionParameter UNION SELECT City, CompanyName, ContactName, Suppliers FROM Suppliers WHERE City=RegionParameter),78/51,下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息 SELECT * FROM dbo.my_function3(Paris) go,79/51,如果用户想通过视图“Customer and Suppl

29、iers by City”来实现同 样的目的，则使用如下查询命令： SELECT * FROM Customer and Suppliers by City WHERE City=Paris go,80/51,11.3.3 多语句表值函数,多语句表值函数的主体中允许使用的语句,赋值 控制流 DECLARE SELECT 游标操作 INSERT UPDATE DELETE EXECUTE,81/51,例: 在Northwind库上利用视图Customer and Suppliers by City，建立一个可以根据输入的城市名和用户要求返回所有该城市的客户或供应商的信息的多语句表值函数my_fu

30、nction4，并将其与上例建立的内嵌表值函数my_function3对比,82/51,USE Northwind GO CREATE FUNCTION my_function4 (RegionParameter nvarchar(25), SelectParameter char(9) RETURNS my_table TABLE (City nvarchar(15) NULL, CompanyName nvarchar(40) NOT NULL, ContactName nvarchar(30) NULL),83/51,AS BEGIN INSERT my_table SELECT City, CompanyName, ContactName FROM Customer and Suppliers by City WHERE City=RegionParameter AND Relationship=SelectParameter RETURN END,84/51,下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息。命令如下： SELECT * FROM