《SQL项目实例教程》-模块九

模块九使用触发器任务描述知识要点返回任务描述触发器与存储过程类似，也是由Ｔ－ＳＱＬ语句组成的。触发器的定义与表紧密相连，当表中数据被修改时，ＳＱＬＳｅｒｖｅｒ自动执行触发器，使用触发器可以实施更为复杂的数据完整性约束。触发器是数据库中比较高级的应用，灵活使用触发器可以大大增强应用程序的健壮性、数据库的可恢复性和数据库的可管理性。任务１创建触发器任务１.１创建ＩＮＳＥＲＴ触发器在“ｅｂｏｏｋ”数据库中，为Ｂｏｏｋ表创建一个名为ＴＲＢｏｏｋＩｎｓｅｒｔ的ＩＮＳＥＲＴ触发器，当用户向Ｂｏｏｋ表插入记录时，如果插入了在ＢｏｏｋＴｙｐｅ表中没有的ｂｏｏｋＴｙｐｅＩｄ，则提示不能插入记录，否则提示记录插入成功。下一页返回任务描述方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建触发器①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。②依次展开“ｅｂｏｏｋ”、“表”、“Ｂｏｏｋ”节点，右击“触发器”节点，在弹出的快捷菜单中选择“新建触发器”命令，如图９.１所示。③选择“新建触发器”命令后，打开查询编辑器窗口，显示创建触发器语句的模板。可以根据任务需求在模板中输入触发器创建文本。上一页下一页返回任务描述④修改完成后，单击工具栏中的“分析”按钮，可检测创建触发器的语法。单击“执行”按钮即可完成触发器的创建。方法二：使用ＣＲＥＡＴＥＴＲＩＧＧＥＲ语句创建触发器在查询编辑器中，输入以下Ｔ－ＳＱＬ命令创建触发器：上一页下一页返回任务描述上一页下一页返回任务描述触发器创建成功后，当用户执行相应的触发动作时，触发器自动执行。本任务中触发器的触发动作为对Ｂｏｏｋ表进行ＩＮＳＥＲＴ操作。下面通过对Ｂｏｏｋ表执行ＩＮＳＥＲＴ语句测试触发器。在查询编辑器中运行如下命令：上一页下一页返回任务描述执行上述语句后，运行结果如图９.２所示。上述语句出错的原因在于向Ｂｏｏｋ表插入记录时，插入了在ＢｏｏｋＴｙｐｅ表中没有的ｂｏｏｋ⁃ＴｙｐｅＩｄ，提示违背了之前创建的外键约束。为了向Ｂｏｏｋ表成功执行以上ＩＮＳＥＲＴ语句，须先向ＢｏｏｋＴｙｐｅ表添加如下记录。ＩＮＳＥＲＴＩＮＴＯＢｏｏｋＴｙｐｅ（ｂｏｏｋＴｙｐｅＩｄ，ｂｏｏｋＴｙｐｅＮａｍｅ）ＶＡＬＵＥＳ（４４，′电力′）向ＢｏｏｋＴｙｐｅ表成功添加记录后，再次向Ｂｏｏｋ表执行ＩＮＳＥＲＴ语句，结果如图９.３所示。上一页下一页返回任务描述运行结果表明，向Ｂｏｏｋ表成功添加数据，触发器也成功执行。通过这个任务可知，ＩＮ⁃ＳＥＲＴ触发器的工作过程如下：①在定义了ＩＮＳＥＲＴ触发器的表上执行ＩＮＳＥＲＴ语句。②ＩＮＳＥＲＴ语句插入的行被记录下来。③触发器动作被执行。任务１.２创建ＵＰＤＡＴＥ触发器在“ｅｂｏｏｋ”数据库中，创建一个ＵＰＤＡＴＥ触发器ＴＲＢｏｏｋＵｐｄａｔｅ，禁止用户修改Ｂｏｏｋ表的ｉｓｂｎ信息。上一页下一页返回任务描述使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建触发器的方法和步骤在任务１.１中已经阐述过了，本任务中直接使用ＣＲＥＡＴＥＴＲＩＧＧＥＲ语句创建触发器。创建触发器的代码如下：上一页下一页返回任务描述触发器创建成功后，当用户执行相应的触发动作时，触发器自动执行。本任务中触发器的触发动作为对Ｂｏｏｋ表进行ＵＰＤＡＴＥ操作。在查询编辑器中运行如下命令测试触发器：上述测试语句执行结果如图９.４所示。上一页下一页返回任务描述测试结果表明Ｂｏｏｋ表的ＵＰＤＡＴＥ触发器成功执行。通过这个任务可知，ＵＰＤＡＴＥ触发器的工作过程如下：①在定义了ＵＰＤＡＴＥ触发器的表上执行ＵＰＤＡＴＥ语句。②ＵＰＤＡＴＥ语句修改前和修改后的行被记录下来。③触发器动作被执行。任务１.３创建ＤＥＬＥＴＥ触发器在“ｅｂｏｏｋ”数据库中，为ＢｏｏｋＴｙｐｅ表创建一个ＤＥＬＥＴＥ触发器ＴＲＢｏｏｋＤｅｌｅｔｅ。要求实现以下功能：当删除ＢｏｏｋＴｙｐｅ表中的某条记录时，自动删除Ｂｏｏｋ表中对应ｂｏｏｋＴｙｐｅＩｄ的记录。上一页下一页返回任务描述实现任务需求的ＣＲＥＡＴＥＴＲＩＧＧＥＲ语句如下：上一页下一页返回任务描述执行结果如图９.５所示。触发器创建成功后，当用户执行相应的触发动作时，触发器自动执行。本任务中触发器的触发动作为对ＢｏｏｋＴｙｐｅ表进行ＤＥＬＥＴＥ操作。在查询编辑器中运行如下命令测试触发器：ＤＥＬＥＴＥＦＲＯＭＢｏｏｋＴｙｐｅＷＨＥＲＥｂｏｏｋＴｙｐｅＩｄ＝４４上一页下一页返回任务描述执行结果如图９.６所示。测试结果表明，删除ＢｏｏｋＴｙｐｅ表中记录时，同时删除了Ｂｏｏｋ表中对应ｂｏｏｋＴｙｐｅＩｄ的记录，ＤＥＬＥＴＥ触发器成功执行。通过这个任务可知，ＤＥＬＥＴＥ触发器的工作过程如下：①在定义了ＤＥＬＥＴＥ触发器的表上执行ＤＥＬＥＴＥ语句。②ＤＥＬＥＴＥ语句删除的行被记录下来。③触发器动作被执行。上一页下一页返回任务描述任务２管理触发器实现对触发器ＴＲＢｏｏｋＩｎｓｅｒｔ和ＴＲＢｏｏｋＵｐｄａｔｅ的查看、修改和禁用启用等。任务２.１查看触发器可以把触发器看作特殊的存储过程，因此所有适用于存储过程的管理方式都适用于触发器。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ查看触发器依赖信息上一页下一页返回任务描述①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理”窗口中，依次展开“数据库”、“ｅｂｏｏｋ”、“表”、“触发器”节点。②在“触发器”节点中，右击需要查看的触发器ＴＲＢｏｏｋＩｎｓｅｒｔ，在弹出的快捷菜单中选择“查看依赖关系”命令。③打开“对象依赖关系”对话框,查看完毕后,单击“确定”按钮。方法二：使用系统存储过程查看触发器信息使用ｓｐ＿ｈｅｌｐ、ｓｐ＿ｈｅｌｐｔｅｘｔ、ｓｐ＿ｄｅｐｅｎｄｓ和ｓｐ＿ｈｅｌｐｔｒｉｇｇｅｒ等系统存储过程来查看触发器的相关信息，在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述执行系统存储过程ｓｐ＿ｈｅｌｐｔｅｘｔ的结果如图９.７所示。另外，也可以使用系统表ｓｙｓｏｂｊｅｃｔｓ查看数据库中所有的触发器，在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述执行结果如图９.８所示。任务２.２修改触发器修改ＴＲＢｏｏｋＩｎｓｅｒｔ触发器，当用户向Ｂｏｏｋ表插入记录时，如果插入了在ＢｏｏｋＴｙｐｅ表中没有的ｂｏｏｋＴｙｐｅＩｄ，则在插入记录之后提示用户加入新的图书类型。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ修改触发器文本上一页下一页返回任务描述在ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ中修改触发器文本的操作步骤如下。①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，依次展开“数据库”、“ｅｂｏｏｋ”、“表”、“触发器”节点。②在“触发器”节点中，右击需要修改的触发器ＴＲＢｏｏｋＩｎｓｅｒｔ，在弹出的快捷菜单中选择“修改”命令。③根据需要，修改触发器命令，具体命令如方法二所示。方法二：使用Ｔ－ＳＱＬ语句修改触发器上一页下一页返回任务描述修改触发器ＴＲＢｏｏｋＩｎｓｅｒｔ，在“查询编辑器”窗口中输入并依次执行如下语句：上一页下一页返回任务描述触发器修改成功后，当用户执行相应的触发动作时，触发器自动执行。本任务中触发器的触发动作为对Ｂｏｏｋ表进行ＩＮＳＥＲＴ操作。下面通过对Ｂｏｏｋ表执行ＩＮＳＥＲＴ语句测试触发器。在查询编辑器中运行如下命令：上一页下一页返回任务描述执行结果如图９.９所示。任务２.３触发器更名将触发器ＴＲＢｏｏｋＩｎｓｅｒｔ更名为ＴＲＢｏｏｋＩｎｓｅｒｔＴｅｓｔ。修改触发器的名称可以使用存储过程ｓｐ＿ｒｅｎａｍｅ，在查询编辑器中运行如下命令：ＥＸＥＣｓｐ＿ｒｅｎａｍｅＴＲＢｏｏｋＩｎｓｅｒｔ，ＴＲＢｏｏｋＩｎｓｅｒｔＴｅｓｔＧＯ执行结果如图９.１０所示。上一页下一页返回任务描述任务２.４禁止和启用触发器对触发器ＴＲＢｏｏｋＵｐｄａｔｅ实现禁用和启用。任务２.４.１禁止触发器禁止表Ｂｏｏｋ上的ＴＲＢｏｏｋＵｐｄａｔｅ触发器，需在查询编辑器中运行如下命令：ＡＬＴＥＲＴＡＢＬＥＢｏｏｋＤＩＳＡＢＬＥＴＲＩＧＧＥＲＴＲＢｏｏｋＵｐｄａｔｅＧＯ执行结果如图９.１１所示。上一页下一页返回任务描述执行之后，可以看到触发器的图标发生了变化，表示此触发器处于不工作状态。触发器不工作时，再修改Ｂｏｏｋ表中记录的ＩＳＢＮ时将不会禁止修改。在查询编辑器中运行如下命令测试触发器：ＵＰＤＡＴＥＢｏｏｋＳＥＴｉｓｂｎ＝′１１１１１１１１１１１１′ＷＨＥＲＥｂｏｏｋＮａｍｅ＝′全球能源互联网′ＧＯ上述测试语句执行结果如图９.１２所示。上一页下一页返回任务描述测试结果表明Ｂｏｏｋ表的ＵＰＤＡＴＥ触发器未能成功执行。通过这个任务可知，ＴＲＢｏｏｋ⁃Ｕｐｄａｔｅ触发器已不工作。任务２.４.２启用触发器启用表Ｂｏｏｋ上的ＴＲＢｏｏｋＵｐｄａｔｅ触发器，需在查询编辑器中运行如下命令：ＡＬＴＥＲＴＡＢＬＥＢｏｏｋＥＮＡＢＬＥＴＲＩＧＧＥＲＴＲＢｏｏｋＵｐｄａｔｅＧＯ执行结果如图９.１３所示。上一页下一页返回任务描述执行之后，可以看到触发器的图标恢复正常，表示此触发器处于工作状态。触发器工作时，再修改Ｂｏｏｋ表中记录的ＩＳＢＮ时将会禁止修改。下面在查询编辑器中运行如下命令测试触发器：上述测试语句执行结果如图９.１４所示。测试结果表明Ｂｏｏｋ表的ＵＰＤＡＴＥ触发器成功执行。通过这个任务可知，ＴＲＢｏｏｋＵｐｄａｔｅ触发器已恢复工作。上一页下一页返回任务描述任务３删除触发器删除Ｂｏｏｋ表的ＴＲＢｏｏｋＵｐｄａｔｅ触发器。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ实现删除触发器在ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ中修改触发器文本的操作步骤如下。①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，依次展开“数据库”、“ｅｂｏｏｋ”、“表”、“触发器”节点。上一页下一页返回任务描述②在“触发器”节点中，右击需要删除的触发器ＴＲＢｏｏｋＵｐｄａｔｅ，在弹出的快捷菜单中，选择“删除”。③在弹出的“删除对象”对话框中单击确定按钮，完成删除操作。方法二：使用Ｔ－ＳＱＬ语句实现删除触发器使用ＤＲＯＰＴＲＩＧＧＥＲ语句可以删除当前数据库中的一个或者多个触发器，在查询编辑器中运行如下命令：上一页下一页返回任务描述执行之后，在触发器列表中将不再有该触发器的图标，表示此触发器已不存在。在Ｂｏｏｋ表中修改记录的ＩＳＢＮ时，将不再禁止修改。在查询编辑器中运行如下命令进行测试：上述测试语句执行结果如图９.１５所示。测试结果表明Ｂｏｏｋ表的ＵＰＤＡＴＥ触发器已不存在。上一页返回知识要点一、触发器概念触发器与存储过程非常相似，也是ＳＱＬ语句集，两者唯一的区别是触发器不能用ＥＸＥ⁃ＣＵＴＥ语句执行，而是在用户执行Ｔ－ＳＱＬ语句时自动触发（激活）执行。下面将对触发器的概念以及类型进行详细介绍。触发器是一个在修改指定表中的数据时执行的存储过程。经常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性或者一致性，由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。下一页返回知识要点触发器不同于前面介绍的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如ＩＮＳＥＲＴ、ＵＰＤＡＴＥ和ＤＥＬＥＴＥ操作时，ＳＱＬＳｅｒｖｅｒ就会自动执行触发器所定义的Ｔ－ＳＱＬ语句，从而确保对数据的处理符合由这些Ｔ－ＳＱＬ语句所定义的规则。１.触发器的作用触发器的主要作用就是能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。上一页下一页返回知识要点它能够对数据库中的相关表进行级联修改，强制比ＣＨＥＣＫ约束更复杂的数据完整性，并自定义错误消息，维护非规范化数据以及比较数据修改前后的状态。与ＣＨＥＣＫ约束不同，触发器可以引用其他表中的列。在下列情况下，使用触发器将强制实现复杂的引用完整性：•强制数据库间的引用完整性。•创建多行触发器，当插入、更新或者删除多行数据时，必须编写一个处理多行数据的触发器。•执行级联更新或级联删除这样的动作。上一页下一页返回知识要点•级联修改数据库中所有相关表。•撤销或者回滚违反引用完整性的操作，防止非法修改数据。２.触发器与存储过程的区别触发器与存储过程主要的区别在于触发器的运行方式。存储过程必须由用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件出现的时候自动执行或者激活的，与连接到数据库中的用户或者应用程序无关。当一行数据被插入、更新或者从表中删除时，触发器才运行，同时这还取决于触发器是怎样创建的。在数据修改时，触发器是强制业务规则的一种很有效的方法。上一页下一页返回知识要点一个表最多有３种不同类型的触发器，当ＵＰＤＡＴＥ发生时使用一个触发器；ＤＥＬＥＴＥ发生时使用一个触发器；ＩＮＳＥＲＴ发生时使用一个触发器。３.触发器的特点触发器具有以下三个特点：•触发器是自动执行的，用户对表中的数据做了任何修改之后立即被激活。•触发器可以通过数据库中的相关表进行层叠更改，实现多个表之间数据的一致性和完整性。上一页下一页返回知识要点•触发器可以强制限制，这些限制比用ＣＨＥＣＫ约束所定义的更复杂。４.触发器的分类在ＳＱＬＳｅｒｖｅｒ２００８数据库系统中，按照触发事件的不同，可以把提供的触发器分成两大类型：ＤＤＬ触发器和ＤＭＬ触发器。（１）ＤＤＬ触发器ＤＤＬ触发器当服务器或者数据库中发生数据定义语言（ＤＤＬ）事件时将被调用。如果执行以下操作，可以使用ＤＤＬ触发器：•防止对数据库架构进行某些更改。上一页下一页返回知识要点•希望数据库中发生某种情况以响应数据库架构中的更改。•记录数据库架构中的更改或者事件。（２）ＤＭＬ触发器ＤＭＬ触发器是当数据库服务器中发生数据操作语言（ＤＭＬ）事件时要执行的操作。通常所说的ＤＭＬ触发器主要包括３种：ＩＮＳＥＲＴ触发器、ＵＰＤＡＴＥ触发器和ＤＥＬＥＴＥ触发器。ＤＭＬ触发器可以查询其他表，还可以包含复杂的Ｔ－ＳＱＬ语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误，则整个事务自动回滚。ＤＭＬ触发器在以下方面非常有用：上一页下一页返回知识要点•ＤＭＬ触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用完整性约束可以更有效地进行这些更改。•ＤＭＬ触发器可以防止恶意或者错误的ＩＮＳＥＲＴ、ＵＰＤＡＴＥ以及ＤＥＬＥＴＥ操作，并强制执行比ＣＨＥＣＫ约束定义的限制更为复杂的其他限制。ＤＭＬ触发器能够引用其他表中的列。•ＤＭＬ触发器可以评估数据修改前后表的状态，并根据该差异采取措施。•一个表中的多个同类ＤＭＬ触发器（ＩＮＳＥＲＴ触发器、ＵＰＤＡＴＥ触发器和ＤＥＬＥＴＥ触发器）允许采取多个不同的操作来响应同一个修改语句。上一页下一页返回知识要点５.Ｄｅｌｅｔｅｄ表和Ｉｎｓｅｒｔｅｄ表ＳＱＬＳｅｒｖｅｒ２００８为每个触发器语句都创建了两种特殊的表：Ｄｅｌｅｔｅｄ表和Ｉｎｓｅｒｔｅｄ表。这是两个逻辑表，由系统来创建和维护，用户不能对它们进行修改。它们存放在内存中而不是数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也会被删除。触发器类型不同，创建的两张临时表的情况和记录都不同，见表９.１。Ｉｎｓｅｒｔｅｄ表存放由于执行ＩＮＳＥＲＴ或者ＵＰＤＡＴＥ语句而要向表中插入的所有行。上一页下一页返回知识要点在执行ＩＮＳＥＲＴ或者ＵＰＤＡＴＥ的操作中，新的行同时添加到触发触发器的表和Ｉｎｓｅｒｔｅｄ表中。Ｉｎｓｅｒｔｅｄ表的内容是触发触发器的表中新行的副本。Ｄｅｌｅｔｅｄ表存放由于执行ＤＥＬＥＴＥ或者ＵＰＤＡＴＥ语句而要从表中删除的所有行。在执行ＤＥＬＥＴＥ或者ＵＰＤＡＴＥ操作时，被删除的行从触发触发器的表中被移动到Ｄｅｌｅｔｅｄ表，这两个表不会有共同的行。二、触发器基本语法１.创建触发器创建一个触发器的基本语法格式如下：上一页下一页返回知识要点上一页下一页返回知识要点其中各主要参数含义如下：•ｔｒｉｇｇｅｒ＿ｎａｍｅ：触发器名字，必须符合ＳＱＬＳｅｒｖｅｒ的命名规则。•ｔａｂｌｅ＿ｎａｍｅ｜ｖｉｅｗ＿ｎａｍｅ：引导触发器的表或视图名，并且该表或视图已经存在。引发触发器的表或视图又称为触发表或触发视图。•ＷＩＴＨＥＮＣＲＹＰＴＩＯＮ：说明对包含ＣＲＥＡＴＥＴＲＩＧＧＥＲ文本的ｓｙｓｃｏｍｍｅｎｔｓ系统表进行加密存储。•ＦＯＲ｜ＡＦＴＥＲ：ＦＯＲ与ＡＦＴＥＲ同义，指定触发器只有在触发ＳＱＬ语句中指定的所有操作都已经成功执行后才激发。上一页下一页返回知识要点所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。•ＩＮＳＴＥＡＤＯＦ：表示可以越过触发语句操作优先执行的触发器。在表或视图上，每个ＩＮＳＥＲＴ、ＵＰＤＡＴＥ、ＤＥＬＥＴＥ语句最多可以定义一个ＩＮＳＴＥＡＤＯＦ触发器。ＩＮＳＴＥＡＤＯＦ触发器不能在ＷＩＴＨＣＨＥＣＫＯＰＴＩＯＮ的可更新视图上定义。但是，在具有ＩＮＳＴＥＡＤＯＦ触发器的视图上可以建立ＩＮＳＴＥＡＤＯＦ触发器。•［ＩＮＳＥＲＴ］［，］［ＵＰＤＡＴＥ］［，］［ＤＥＬＥＴＥ］：定义触发事件。上一页下一页返回知识要点至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且各选项要用逗号隔开。•ＮＯＴＦＯＲＲＥＰＬＩＣＡＴＩＯＮ：表示当复制进程更改触发器所涉及的表时，不要执行该触发器。•ＡＳｓｑｌ＿ｓｔａｔｅｍｅｎｔ：指定触发器将要执行的操作，包含条件语句或处理语句。触发器的条件语句定义了另外的标准来决定将被执行的ＩＮＳＥＲＴ、ＤＥＬＥＴＥ、ＵＰＤＡＴＥ语句是否激活触发器。•ＩＦＵＰＤＡＴＥ：测试在指定的列上进行的ＩＮＳＥＲＴ或ＵＰＤＡＴＥ操作，不能用于ＤＥＬＥＴＥ操作，可以指定多列。上一页下一页返回知识要点２.管理触发器的语法（１）查看触发器使用系统存储过程ｓｐ＿ｈｅｌｐ、ｓｐ＿ｈｅｌｐｔｅｘｔ和ｓｐ＿ｄｅｐｅｎｄｓ可以分别查看触发器的不同信息。ｓｐ＿ｈｅｌｐ显示触发器的所有者和创建时间。ｓｐ＿