触发器操作PPT课件

1、SQL Server SQL Server 实 例 教 程实 例 教 程(20 0 8(20 0 8 版版 ) ) 触发器操作SQL ServerSQL Server案例教程（案例教程（20082008版）版）本章导航本章导航SQL ServerSQL Server案例教程（案例教程（20082008版）版）本章任务描述本章任务描述任 务 编 号子 任 务任 务 内 容任务1了解inserted表和deleted表任务1-1了解Goods表中添加一条商品号为“888888”记录时，inserted表和deleted表的变化情况任务1-2了解Goods表中删除一条商品号为“030001”记录时，

2、inserted表和deleted表的变化情况任务1-3了解Goods表中将商品号为“888888”商品号修改成“999999”时，inserted表和deleted表的变化情况任务2使用SSMS实现对触发器的创建、修改、查看和删除等操作任务2-1创建一个添加记录后显示提示信息的触发器tr_notify任务2-2禁用触发器tr_notify任务3使用T-SQL 语句实现对触发器的创建、修改、查看和删除等操作任务3-1创建Users表的删除触发器tr_delele任务3-2创建作用于数据库的修改表事件的触发器tr_altertable任务3-3创建一个作用于连接SQL Server实例时的登录触

3、发器tr_testlogin 任务3-4修改“任务3-1”中创建的触发器tr_delele任务3-5了解Users表中所有触发器的相关信息，并显示触发器tr_notify的文本信息任务3-6禁用触发器tr_delete，并通过删除名为“amy”的用户验证触发器的工作任务3-7删除Users表中的tr_notify触发器和WebShop数据库中的触发器tr_altertable任务4在SQL Server 2008中使用触发器实施参照完整性和特殊业务规则任务4-1创建插入订单记录时实施参照完整性的触发器tr_placeorder任务4-2 创建订单详情表中的商品信息发生变化时，要自动更新订单表中

4、的订单总金额的触发器tr_sumSQL ServerSQL Server案例教程（案例教程（20082008版）版） 触发器简介。触发器是一种特殊类型的存储触发器简介。触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效，过程，它在指定的表中的数据发生变化时自动生效，触发器被调用时自动执行触发器被调用时自动执行INSERTINSERT、UPDATEUPDATE、DELETEDELETE语句和语句和SELECTSELECT语句，实现表间的数据完整性和复杂语句，实现表间的数据完整性和复杂的业务规则。的业务规则。（一）触发器基本知识（一）触发器基本知识SQL ServerSQL S

5、erver案例教程（案例教程（20082008版）版）根据服务器或数据库中调用触发器的操作不同，根据服务器或数据库中调用触发器的操作不同，SQL SQL Server 2008Server 2008的触发器分为的触发器分为DMLDML触发器、触发器、DDLDDL触发器和登录触发器和登录触发器三种。触发器三种。 （1 1）DML DML 触发器是当数据库服务器中发生数据操作语言触发器是当数据库服务器中发生数据操作语言（DMLDML）事件时要执行的操作。）事件时要执行的操作。 （2 2）DDLDDL触发器是是一种特殊的触发器，当服务器或数据库触发器是是一种特殊的触发器，当服务器或数据库中发生数据定

6、义语言中发生数据定义语言 (DDL) (DDL) 事件时将调用事件时将调用 DDL DDL 触发器。触发器。 （3 3）登录触发器将为响应）登录触发器将为响应 LOGON LOGON 事件而激发存储过程。与事件而激发存储过程。与 SQL Server SQL Server 实例建立用户会话时将引发此事件。实例建立用户会话时将引发此事件。 （二）触发器类型（二）触发器类型触发器概述触发器概述SQL ServerSQL Server案例教程（案例教程（20082008版）版） 通过记录的添加、删除和修改等操作，了解通过记录的添加、删除和修改等操作，了解触发器执行时触发器执行时inserted表和表

7、和deleted表的情况。表的情况。（二）（二）insertedinserted表和表和deleteddeleted表表触发器概述触发器概述SQL ServerSQL Server案例教程（案例教程（20082008版）版）1 1、INSERT INSERT 触发器触发器 触发器概述触发器概述SQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】insertedinserted和和deleteddeleted表不能直接被读取，因为这两表不能直接被读取，因为这两个表是存在于内存中，也就是说是在执行插入、个表是存在于内存中，也就是说是在执行插入、删除和修

8、改操作过程中，这两个表才存在。删除和修改操作过程中，这两个表才存在。 而只有在触发器中才能捕获这一动态过程（事而只有在触发器中才能捕获这一动态过程（事务），所以务），所以insertedinserted和和deleteddeleted表的读取也只能在表的读取也只能在触发器中实现。触发器中实现。 上述结果是在触发器中对上述结果是在触发器中对insertedinserted表和表和deleteddeleted表表查询（如查询（如SELECTSELECT* *FROM insertedFROM inserted）的结果。）的结果。 触发器概述触发器概述SQL ServerSQL Server案例教程

9、（案例教程（20082008版）版）2 2、DELETE DELETE 触发器触发器 触发器概述触发器概述SQL ServerSQL Server案例教程（案例教程（20082008版）版） （四）（四）UPDATEUPDATE触发器触发器触发器概述触发器概述SQL ServerSQL Server案例教程（案例教程（20082008版）版）使用使用 SSMS SSMS 管理触发器管理触发器 在在SQL Server 2008中使用中使用“SQL Server Management Studio”实现对触发器的创建、修实现对触发器的创建、修改、查看和删除等操作。改、查看和删除等操作。SQL S

10、erverSQL Server案例教程（案例教程（20082008版）版）一、创建触发器一、创建触发器 use WebShopif OBJECT_ID(users.tr_notify,TR) is not nulldrop trigger users.tr_notifygocreate trigger tr_notifyon usersafter insertasprint 友情提示：表中增加新记录Goinsert into Users values(trigger,trigger,普通,trigger)SQL ServerSQL Server案例教程（案例教程（20082008版）版）一、创

11、建触发器一、创建触发器 【提示提示】 在查询窗口中创建触发器实质上是通过在查询窗口中创建触发器实质上是通过T-SQLT-SQL语句创建触发器。语句创建触发器。 触发器创建后，在指定的条件下会自动触发。触发器创建后，在指定的条件下会自动触发。 【任务任务2-12-1】创建的触发器为创建的触发器为DMLDML触发器。触发器。 SQL ServerSQL Server案例教程（案例教程（20082008版）版）二、禁用、修改和删除触发器二、禁用、修改和删除触发器 【提示提示】 在图中选择在图中选择【修改修改】可以完成可以完成触发器的修改操作。触发器的修改操作。 在图中选择在图中选择【删除删除】可以删

12、除可以删除指定的触发器。指定的触发器。 触发器被禁用后，图中的触发器被禁用后，图中的【启启用用】菜单项将变为可用，并可菜单项将变为可用，并可通过该菜单项启用指定的触发通过该菜单项启用指定的触发器。器。 SQL ServerSQL Server案例教程（案例教程（20082008版）版） 二、禁用、修改和删除触发器二、禁用、修改和删除触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版）【上机实践1】操作要求：操作要求：（1 1）对）对GoodsGoods表创建插入触发器表创建插入触发器tr_inserttr_insert，实现显示，实现显示GoodsGood

13、s表、表、insertedinserted表和表和deleteddeleted表中记表中记录的功能。录的功能。（2 2）往）往GoodsGoods表中插入一条商品号为表中插入一条商品号为“111111111111”的商品记录，验证触发器的执行。的商品记录，验证触发器的执行。（3 3）对）对GoodsGoods表创建修改触发器表创建修改触发器tr_updatetr_update，实现显示，实现显示GoodsGoods表、表、insertedinserted表和表和deleteddeleted表中记表中记录的功能。录的功能。（4 4）将）将GoodsGoods表中商品号为表中商品号为“111111

14、111111”的商品号修改为的商品号修改为“222222222222”，记录验证触发器的执行。，记录验证触发器的执行。（5 5）禁用触发器）禁用触发器tr_updatetr_update。（6 6）将）将GoodsGoods表中商品号为表中商品号为“222222222222”的商品号修改为的商品号修改为“333333333333”，记录验证触发器的执行。，记录验证触发器的执行。（7 7）对）对GoodsGoods表创建删除触发器表创建删除触发器tr_deletetr_delete，实现显示，实现显示GoodsGoods表、表、insertedinserted表和表和deleteddeleted

15、表中记表中记录的功能。录的功能。（8 8）删除）删除GoodsGoods表中商品号为表中商品号为“222222222222”的商品记录，验证触发器的执行。的商品记录，验证触发器的执行。（9 9）删除所建的触发器）删除所建的触发器tr_inserttr_insert、tr_updatetr_update和和tr_deletetr_delete。 SQL ServerSQL Server案例教程（案例教程（20082008版）版）使用使用 T-SQL T-SQL 管理触发器管理触发器 在在SQL Server 2008中使用中使用T-SQL 语句完成语句完成触发器的创建、修改、查看和删除等操作。触

16、发器的创建、修改、查看和删除等操作。SQL ServerSQL Server案例教程（案例教程（20082008版）版）一、创建触发器一、创建触发器 创建创建DMLDML触发器基本语句格式如下：触发器基本语句格式如下：CREATE TRIGGER 触发器名触发器名ON 表表 视图视图FORAFTERINSTEAD OF INSERTUPDATEDELETEASDML语句语句SQL ServerSQL Server案例教程（案例教程（20082008版）版） 创建创建DDLDDL触发器基本语句格式如下：触发器基本语句格式如下：CREATE TRIGGER 触发器名触发器名ON ALL SERVE

17、R | DATABASEFORAFTER DDL事件事件 ASDDL语句语句一、创建触发器一、创建触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版） 创建登录触发器基本语句格式如下：创建登录触发器基本语句格式如下：CREATE TRIGGER触发器名触发器名ON ALL SERVER FOR | AFTER LOGON AS SQL语句语句一、创建触发器一、创建触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版） 一、创建触发器一、创建触发器use WebShopgocreate trigger tr_deleteo

18、n usersfor deleteasbegindeclare user varchar(30)select user=u_name from deletedprint user+用户已被删除Enddelete from Users where u_Name=triggerSQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】 CREATE TRIGGER CREATE TRIGGER 语句必须是批处理中的第一个语句，该语句后面的所有其语句必须是批处理中的第一个语句，该语句后面的所有其他语句被解释为他语句被解释为 CREATE TRIGGER CRE

19、ATE TRIGGER 语句定义的一部分。语句定义的一部分。 创建创建 DML DML 触发器的权限默认分配给表的所有者，且不能将该权限转给其他触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。用户。 DML DML 触发器为数据库对象，其名称必须遵循标识符的命名规则。触发器为数据库对象，其名称必须遵循标识符的命名规则。 虽然虽然 DML DML 触发器可以引用当前数据库以外的对象，但只能在当前数据库中触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建创建 DML DML 触发器。触发器。 虽然虽然 DML DML 触发器可以引用临时表，但不能对临时表或系统表创建触发器可

20、以引用临时表，但不能对临时表或系统表创建 DML DML 触发触发器。同时不应引用系统表，而应使用信息架构视图。器。同时不应引用系统表，而应使用信息架构视图。 对于含有用对于含有用 DELETE DELETE 或或 UPDATE UPDATE 操作定义的外键的表，不能定义操作定义的外键的表，不能定义 INSTEAD INSTEAD OF DELETE OF DELETE 和和 INSTEAD OF UPDATE INSTEAD OF UPDATE 触发器。触发器。 TRUNCATE TABLE TRUNCATE TABLE 语句不会触发语句不会触发 DELETE DELETE 触发器，因为触发

21、器，因为 TRUNCATE TABLE TRUNCATE TABLE 语语句没有执行记录。索引名必须符合标识符规则。句没有执行记录。索引名必须符合标识符规则。一、创建触发器一、创建触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】 在编写在编写DDLDDL触发器时要了解用于激发触发器时要了解用于激发 DDL DDL 触发器的触发器的 DDL DDL 事件，请参事件，请参阅联机丛书。阅联机丛书。 DDLDDL触发器作用于数据库或服务器，不同于触发器作用于数据库或服务器，不同于DMLDML触发器作用于表。触发器作用于表。一、创建触发器一、创建触

22、发器use WebShopgocreate trigger tr_altertableon databasefor alter_tableasbeginprint 数据表已被修改Endalter table users add test char(8)SQL ServerSQL Server案例教程（案例教程（20082008版）版） 一、创建触发器一、创建触发器use mastergocreate login login_test with password=123456gogrant view server state to login_testgocreate trigger tr_te

23、stloginon all server with execute as login_testfor logonasbeginif ORIGINAL_LOGIN()=login_testrollbackendSQL ServerSQL Server案例教程（案例教程（20082008版）版） 一、创建触发器一、创建触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】 【任务任务3-33-3】中涉及到数据库安全相关的中涉及到数据库安全相关的SQLSQL语句，请参阅第语句，请参阅第1111章。章。 DDLDDL触发器和登录触发器通过使用触发器和登

24、录触发器通过使用 T-SQL T-SQL 的的EVENTDATAEVENTDATA函数来获取有关触发函数来获取有关触发事件的信息。事件的信息。 若要创建若要创建 DML DML 触发器，则需要对要创建触发器的表或视图具有触发器，则需要对要创建触发器的表或视图具有 ALTER ALTER 权权限。限。 若要创建具有服务器范围的若要创建具有服务器范围的 DDL DDL 触发器触发器 (ON ALL SERVER) (ON ALL SERVER) 或登录触发器，或登录触发器，则需要对服务器拥有则需要对服务器拥有 CONTROL SERVER CONTROL SERVER 权限。权限。 若要创建具有数

25、据库范围的若要创建具有数据库范围的 DDL DDL 触发器触发器 (ON DATABASE)(ON DATABASE)，则需要在当前数，则需要在当前数据库中有据库中有 ALTER ANY DATABASE DDL TRIGGER ALTER ANY DATABASE DDL TRIGGER 权限。权限。 DMLDML触发器作用于表或视图，触发器作用于表或视图，DDLDDL触发器作用于数据库或服务器，登录触发触发器作用于数据库或服务器，登录触发器作用于服务器。器作用于服务器。 一、创建触发器一、创建触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版）（一）修

26、改触发器（一）修改触发器 二、修改和查看触发器二、修改和查看触发器修改修改DML触发器，基本语句格式如下：触发器，基本语句格式如下： ALTER TRIGGER 触发器名触发器名ON 表表 视图视图FORAFTERINSTEAD OF INSERTUPDATEDELETEASDML语句语句SQL ServerSQL Server案例教程（案例教程（20082008版）版） 二、修改和查看触发器二、修改和查看触发器修改修改DDL触发器的基本语句格式如下：触发器的基本语句格式如下： ALTER TRIGGER 触发器名触发器名ON ALL SERVER | DATABASEFORAFTER DDL

27、事件事件 ASDDL语句语句SQL ServerSQL Server案例教程（案例教程（20082008版）版） 二、修改和查看触发器二、修改和查看触发器修改登录触发器的基本语句格式如下：修改登录触发器的基本语句格式如下： ALTER TRIGGER触发器名触发器名ON ALL SERVER FOR | AFTER LOGON AS SQL语句语句use WebShopgoalter trigger tr_deleteon usersfor deleteasbegindeclare user varchar(30)select user=u_name from deletedprint 注意：

28、+user+用户已被删除Enddelete from Users where u_Name=triggerSQL ServerSQL Server案例教程（案例教程（20082008版）版） 使用系统存储过程sp_helptrigger和sp_helptext可以查看触发器，但作用有所差异：使用sp_helptrigger返回的是触发器的类型，而使用sp_helptext则显示触发器的文本。 （二）查看触发器（二）查看触发器二、修改和查看触发器二、修改和查看触发器use WebShopgosp_helptrigger usersgosp_helptext tr_notifySQL Server

29、SQL Server案例教程（案例教程（20082008版）版）【提示提示 sp_helptriggersp_helptrigger 不能用于不能用于 DDL DDL 触发器。触发器。 isdeleteisdelete表示是否删除触发器，表示是否删除触发器，isupdateisupdate表示是否修改触发器，表示是否修改触发器，isinsertisinsert表示是否插入触发器，表示是否插入触发器，isafterisafter表示是否表示是否afterafter触发器，触发器，isinsteadofisinsteadof表示是否表示是否insteadofinsteadof触发器。触发器。 二、

30、修改和查看触发器二、修改和查看触发器SQL ServerSQL Server案例教程（案例教程（20082008版）版）三、禁用三、禁用/ /启用和删除触发器启用和删除触发器 【提示提示】 默认情况下，创建触发器后会启用触发器。默认情况下，创建触发器后会启用触发器。 禁用触发器不会删除该触发器，该触发器仍然作为对象存在于当前数据库禁用触发器不会删除该触发器，该触发器仍然作为对象存在于当前数据库中。中。 禁用触发器后，执行相应的禁用触发器后，执行相应的T-SQL T-SQL 语句时，不会引发触发器。语句时，不会引发触发器。 使用使用ENABLE TRIGGER ENABLE TRIGGER 可以

31、重新启用可以重新启用 DML DML 和和 DDL DDL 触发器。触发器。 也可以使用也可以使用 ALTER TABLE ALTER TABLE 语句来禁用或启用为表所定义的语句来禁用或启用为表所定义的 DML DML 触发器。触发器。（一）禁用触发器（一）禁用触发器disable trigger tr_delete on usersSQL ServerSQL Server案例教程（案例教程（20082008版）版）（二）启用触发器（二）启用触发器 基本语句格式如下：基本语句格式如下：ENABLE TRIGGER ENABLE TRIGGER 触发器名触发器名 ，n | ALL n | AL

32、L ON ON 对象名对象名 | |数据库数据库 | | 服务器服务器 三、禁用三、禁用/ /启用和删除触发器启用和删除触发器enable trigger tr_delete on usersSQL ServerSQL Server案例教程（案例教程（20082008版）版）（三）（三）T-SQL T-SQL 删除触发器删除触发器 【提示提示】 可以通过删除可以通过删除 DML DML 触发器或删除触发器表来删除触发器或删除触发器表来删除 DML DML 触触发器。发器。 仅当所有触发器均使用相同的仅当所有触发器均使用相同的 ON ON 子句创建时，才能使用子句创建时，才能使用一个一个 DRO

33、P TRIGGER DROP TRIGGER 语句删除多个语句删除多个 DDL DDL 触发器。触发器。三、禁用三、禁用/ /启用和删除触发器启用和删除触发器use WebShopgodrop trigger tr_notify godrop trigger tr_altertable on databasegoSQL ServerSQL Server案例教程（案例教程（20082008版）版）触发器的应用触发器的应用 在在SQL Server 2008中使用触发器实施参照中使用触发器实施参照完整性和特殊业务规则。完整性和特殊业务规则。SQL ServerSQL Server案例教程（案例教程

34、（20082008版）版）一、实施参照完整性一、实施参照完整性 use WebShopgocreate trigger tr_placeorderon ordersfor insert,updateasbegindeclare p_no char(6)declare c_no char(5)select p_no=payments.p_Idfrom Payments,insertedwhere Payments.p_Id=inserted.p_Idselect c_no=Customers.c_IDfrom Customers,insertedwhere Customers.c_ID=inse

35、rted.c_IDif p_no is null or c_no is nullbeginrollbackraiserror(不存在这样的支付方式或会员,16,10)endendSQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】 如果创建了外键约束，则在触发器执行前外键会起作用，为如果创建了外键约束，则在触发器执行前外键会起作用，为了验证该触发器的功能，可以先删除了验证该触发器的功能，可以先删除OrdersOrders表的约束。表的约束。 tr_placeordertr_placeorder触发器的引发操作是触发器的引发操作是INSERTINSE

36、RT和和UPDATEUPDATE，请读，请读者自行使用者自行使用INSERTINSERT操作验证该触发器。操作验证该触发器。 在创建触发器的语句中使用了在创建触发器的语句中使用了“ROLLBACK TRANSACTIONROLLBACK TRANSACTION”，表示取消所执行的操作（这里为表示取消所执行的操作（这里为UPDATEUPDATE），该语句的详细使），该语句的详细使用请参阅本书第用请参阅本书第1010章。章。 触发器中对于参照表中触发器中对于参照表中p_IDp_ID和和c_IDc_ID的判断，也可以使用存的判断，也可以使用存在性检查语句实现，请参考以下语句。在性检查语句实现，请参考

37、以下语句。一、实施参照完整性一、实施参照完整性SQL ServerSQL Server案例教程（案例教程（20082008版）版）二、实施特殊业务规则二、实施特殊业务规则 use WebShopgocreate trigger tr_sumon orderdetailsfor insert,updateasbeginupdate Ordersset o_Sum=(select SUM(d_price*d_number)from OrderDetailswhere o_ID=(select o_ID from inserted)where o_ID=(select o_ID from inser

38、ted)EndGoinsert into OrderDetails(o_ID,g_ID,d_Price,d_Number)values(200708022045,030003,2520,2)SQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】在在OrderDetailsOrderDetails表的插入语句中的表名之后指定表的插入语句中的表名之后指定列名表，是考虑到列名表，是考虑到“d_IDd_ID”为自动编号，不需要明为自动编号，不需要明确指定。确指定。图中的图中的“d_IDd_ID”号从号从12121515是由于该列为自动编号，是由于该列为自动编

39、号，中间曾经插入过两条记录，后被删除引起。中间曾经插入过两条记录，后被删除引起。这些特殊的业务规则也可以由应用程序员编写程这些特殊的业务规则也可以由应用程序员编写程序来完成。序来完成。 二、实施特殊业务规则二、实施特殊业务规则SQL ServerSQL Server案例教程（案例教程（20082008版）版）【上机实践2】 操作要求：操作要求：（1 1）在）在WebShopWebShop中创建删除表触发器（中创建删除表触发器（DDLDDL触发器）触发器）tr_droptabletr_droptable，并试着删除并试着删除PaymetsPaymets表验证触发器的执行情况。表验证触发器的执行情

40、况。（2 2）在）在CustomersCustomers表中创建触发器表中创建触发器tr_deletecusttr_deletecust，实现在，实现在CustomersCustomers表中删除会员号为表中删除会员号为“C0004C0004”的会员时，将该会员所下的会员时，将该会员所下达的订单（在达的订单（在OrdersOrders表中）全部删除。表中）全部删除。（3 3）在）在OrdersOrders表中创建触发器表中创建触发器tr_deleteordertr_deleteorder，实现在，实现在OrdersOrders表表中删除订单号为中删除订单号为“20070802153320070

41、8021533”的订单记录时，将该订单所包的订单记录时，将该订单所包含的详细信息（在含的详细信息（在OrderDetailsOrderDetails表中）全部删除。表中）全部删除。SQL ServerSQL Server案例教程（案例教程（20082008版）版）（4 4）在）在OrdersOrders表中创建触发器表中创建触发器tr_detailtr_detail，实现在订单表中添，实现在订单表中添加一条记录号为加一条记录号为“200708080808200708080808”的记录时，在的记录时，在OrderDetailsOrderDetails表中添加如下两条记录。表中添加如下两条记录。（200708080808200708080808，030003030003，25202520，1 1）（200708080808200708080808，010008010008，27002700，2 2）（5 5）删除所建的触发器）删除所建的触发器tr_droptabletr_droptable