《SQL Server数据库项目10教学ppt课件.pptx》由会员分享，可在线阅读，更多相关《SQL Server数据库项目10教学ppt课件.pptx（30页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、SQL Server数据库项目10教学课件PART01项目1初识数据库PART02项目2SQL Server 2017的安装与使用PART03项目3数据库操作PART05项目5查询操作PART06项目6T-SQL语言PART07项目7视图操作目录CONTENTSPART04项目4数据表操作PART08项目8数据完整性PART09项目9存储过程PART10项目10触发器PART11项目11备份与恢复PART12项目12数据库安全PART10项目10触发器10.1 创建DML触发器10.2 管理DML触发器10.3 创建及管理DDL触发器任务10.1 创建DML触发器10.1 创建DML触发器【任
2、【任务描述】描述】在在studentstudent数据数据库中的中的student_tablestudent_table表中添加一个表中添加一个“CreateDate”“CreateDate”字段，用来字段，用来记录数据被修改的数据被修改的时间。如果每次修改数据后都手工更新如果每次修改数据后都手工更新这个数据，即耗个数据，即耗费时间和精力，和精力，还很很难准确准确记录当当时的的时间。触触发器正好解决了器正好解决了这一一问题。在表。在表student_tablestudent_table中中创建了触建了触发器后，一旦器后，一旦该表的数据表的数据发生了生了变化，触化，触发器会自器会自动将当前的系将
3、当前的系统时间更新到更新到“CreateDate”“CreateDate”字段中。字段中。【任【任务分析】分析】通俗地通俗地讲，触，触发器的原理就是当器的原理就是当设定的一个事件定的一个事件发生生时去自去自动执行另一个事件。本任行另一个事件。本任务比比较简单，只，只要注意分清触要注意分清触发事件和被触事件和被触发事件即可。事件即可。完成完成该任任务需要做到以下几点：需要做到以下几点：（1 1）了解触）了解触发器的含器的含义和作用。和作用。（2 2）掌握在）掌握在SQLServer2017SQLServer2017的的资源管理器中源管理器中创建触建触发器的方法。器的方法。（3 3）掌握）掌握创建
4、的触建的触发器的器的执行方法。行方法。10.1 创建DML触发器【任【任务实现】步步骤0101：启：启动触触发器器编辑窗口。在窗口。在SSMSSSMS中依次展开中依次展开“student”“student”“表表”“student_table”“student_table”节点，右点，右击“触触发器器”子子节点，在点，在弹出的快捷菜出的快捷菜单中中选择“新建触新建触发器器”，打开触，打开触发器器编辑窗口，如窗口，如图10-110-1所示。所示。10.1 创建DML触发器步骤02：系统自动在触发器编辑窗口中生成创建触发器的模板语句及注释，这些语句和缩写的功能是架构环境和说明程序。这里不使用系统提
5、供的语句模板，将语句全部删除，然后手动输入如下创建存储过程的语句。CREATE TRIGGER student_trigOn student_tableAFTER INSERTASBEGIN IF(COLUMNS_UPDATED()IS NOT NULL)UPDATE student_table SET CreateDate=GETDATE()FROM student_table n,inserted i WHERE n.student_id=i.student_idEND10.1 创建DML触发器 编写好后，写好后，执行行语句，完成触句，完成触发器的器的创建。建。CREATE TRIGGER
6、 student_trigOn student_tableAFTER INSERTASBEGIN IF(COLUMNS_UPDATED()IS NOT NULL)UPDATE student_table SET CreateDate=GETDATE()FROM student_table n,inserted i WHERE n.student_id=i.student_idEND10.1 创建DML触发器步骤03：运行及验证触发器。首先查看student_table表未修改时的原表数据。在查询编辑器中输入如下语句：SELECT*FROM student_table打开student_tabl
7、e表，查看其中的数据。需要注意的是，此时两条数据的“CreateDate”字段内的值都是NULL，如图10-2所示。10.1 创建DML触发器使用数据修改语句修改student_table表中的数据，将“student_ID”为“20200101”的记录中“Name”字段的数值改为“崔伟康”UPDATEstudent_tableSETName=崔伟康WHEREstudent_ID=20200101执行结果如图10-3所示。可以看到，此时“student_ID”为“20200101”的记录的“CreateDate”字段中的数值已经自动变为系统日期，证明了触发器的有效性。10.1 创建DML触发器
8、【相关知识】1.触发器的概念和作用触发器是与数据库和数据表相结合的特殊的存储过程，当数据表发生Insert、Update、Delete操作或数据库发生Create、Alter、Drop操作的时候，可以激活触发器，并运行其中的T-SQL语句。触发器实际上是一种特殊类型的存储过程，其特殊性表现在：它是在执行某些特定的T-SQL语句时自动激活的。2.触发器的分类触发器分为DML触发器和DDL触发器两种。这里主要介绍DML触发器。DML触发器又分为After触发器和InsteadOf触发器两种。After触发器是先修改记录后激活的触发器；InsteadOf触发器是“取代”触发器。After触发器只能用
9、在数据表中，而InsteadOf触发器即可以用在数据表中，也可以用在视图中。DDL触发器根据作用范围可以分为作用在数据库的触发器和作用在服务器的触发器两种。3.DML触发器DML触发器有助于数据库在修改数据的时候实施强制性规则，保证数据的完整性，其主要功能如下：（1）可以完成比CHECK更复杂的约束要求。（2）拒绝违反引用完整性的数据操作，保证数据准确性。（3）比较表修改前后的数据变化，并根据变化采取相应操作。（4）可级联修改具有关系的表。DML触发器主要针对的操作有3种：UPDATE、INSERT、DELETE。若某个表格具有触发器，则对该表格进行更新、插入或删除操作时，就会触发对应的UPD
10、ATE、INSERT或DELETE触发器。10.1 创建DML触发器4.DELETED和INSERTED表系统会自动为触发器创建两个系统临时表：DELETED和INSERTED。触发器运行过程中所涉及的数据都会根据不同的操作类型保存在相应的表中，触发器会在需要的时候调用其中的数据，用户也可以从表中调用数据。每个触发器只能调用对应表中的数据。DELETED和INSERTED虽被称为表，其实并不同于一般的数据库表，他们储存在内存中，而非在磁盘上。两个表的结构类似于定义触发器的表结构。DELETED表会储存被DELETE及UPDATE语句影响的行副本。触发器被删除或更新时，被删除或更新的行会传送到D
11、ELETED表，触发器和用户即可以使用DELETED表中的数据。INSERTED表会储存被INSERT及UPDATE语句影响的行副本，在插入或更新事务时，新的行会被同时加至触发器表与INSERTED表。由于执行UPDATE语句时，会被视为插入或删除事务，因此旧的行值会保留一份副本在DELETED表中，而新的行值的副本则保留在触发器表与INSERTED表。DELETED和INSERTED表中的值只限于在触发器中使用，一旦触发器完成就无法再使用。10.1 创建DML触发器5.创建触发器语句创建触发器可以使用CREATETRIGGER语句，其语法格式如下：CREATETRIGGER触发器名ON表名|
12、视图名WITHENCRYPTIONFORDELETE,INSERT,UPDATEASIFUPDATE(字段名).AND|ORUPDATE(字段名).Nsql_statement.N参数说明如下：WITHENCRYPTION：加密选项，防止触发器作为SQLServer复制的一部分发布。DELETE,INSERT,UPDATE：表示指定执行哪些语句时将激活触发器，至少要指定一个选项，若选项多于一个，需用逗号分隔各选项。IFUPDATE（字段名）：测试在制定的字段上进行的INSERT或UPDATE操作，不能用于DELETE操作。对INSERT操作，测试将返回TRUE值，因为在指定字段上输入了显性值或
13、隐性（NULL）值。若要同时对多个字段进行测试，可使用逻辑运算符链接多个UPDATE（字段名）子句。10.1 创建DML触发器【例【例10-110-1】为表表创建触建触发器器本本实例要求在例要求在student_tablestudent_table表中表中删除某条信息除某条信息时，用数据，用数据输出出语句提示用句提示用户，并，并显示示该条被条被删除的数据信息。除的数据信息。步骤步骤0101：创建触发器。在查询编辑器中输入如下语句：CREATE TRIGGER DEL_triON student_tableAFTER DELETEASprint 删除的信息为：SELECT*FROM delete
14、d10.1 创建DML触发器步步骤0202：验证触触发器。器。为了了验证触触发器的功能，在器的功能，在查询编辑器中运行如下器中运行如下语句，句，删除除student_tablestudent_table表中的一条数据，激活触表中的一条数据，激活触发器，会得到如器，会得到如图10-510-5所示的所示的结果。果。DELETE FROM student_table WHERE student_id=20200103任务10.2 管理DML触发器10.2 管理DML触发器【任【任务描述】描述】DMLDML触触发器器创建完成后，其后建完成后，其后续的管理工作主要包括修改、禁用、启用、的管理工作主要包括
15、修改、禁用、启用、删除。本任除。本任务将将对这4 4项工作工作进行行讲解。解。【任【任务分析】分析】本任本任务涉及涉及对触触发器的修改、禁用、启用、器的修改、禁用、启用、删除操作，其中，修改触除操作，其中，修改触发器的器的语法格式与法格式与创建触建触发器的基本相同；禁用、启用和器的基本相同；禁用、启用和删除都可以通除都可以通过SSMSSSMS的的对象象资源管理器来源管理器来实现。10.2 管理DML触发器【任【任务实现】1.1.修改触修改触发器器将前面触将前面触发器中的器中的“只要只要NewsNews表中数据修改就触表中数据修改就触发”改改为“只有只有AuthorAuthor字段中的数据修改才
16、触字段中的数据修改才触发”。步步骤0101：在：在SSMSSSMS中依次展开中依次展开“OASystem”“OASystem”“News”“News”“触触发器器”节点，右点，右击触触发器器“News_tri”“News_tri”子子节点，在点，在弹出的快捷菜出的快捷菜单中中选择“修改修改”，打开触，打开触发器器编辑窗口。窗口。步步骤0202：窗口中的：窗口中的语句与句与创建触建触发器器时的的语句基本相同，只是将句基本相同，只是将“CREATE”“CREATE”语句改句改为“ALTER”“ALTER”语句，如句，如图10-510-5所示。用所示。用户可以根据新的需求可以根据新的需求对语句句进行
17、修改。行修改。步步骤0303：根据任：根据任务要求，需要修改触要求，需要修改触发器触器触发条件，将原来的任意修改触条件，将原来的任意修改触发变为只有只有“Author”“Author”字段的字段的值修改才触修改才触发。将原。将原语句中的句中的“IF(COLUMNS_UPDATED()isNotNull)”“IF(COLUMNS_UPDATED()isNotNull)”修改修改为“IFUPDATE(Author)”“IFUPDATE(Author)”，步步骤0404：通：通过数据的修改来数据的修改来验证其有效性，例如，分其有效性，例如，分别修改修改“News”“News”表中的表中的“Autho
18、r”“Author”字段和另一个字段来字段和另一个字段来验证本次修改的效果。本次修改的效果。10.2 管理DML触发器2.禁用触发器若暂时不需要“student_tri”触发器发挥作用，但是又不想删除它，可以将其禁用。步骤01：在SSMS中依次展开“student”“student_table”“触发器”节点，右击触发器“student_tri”子节点，在弹出的快捷菜单中选择“禁用”，打开“禁用触发器”对话框。步骤02：“禁用触发器”对话框将显示禁用的结果及相关信息，如图10-6所示。单击“关闭”按钮，实现对“student_tri”触发器的禁用。10.2 管理DML触发器3.启用触发器如果需
19、要让已经被禁用的触发器重新发挥作用，需要重新启用该触发器。步骤01：在SSMS中依次展开“student”“student_table”“触发器”节点，右击触发器“student_tri”子节点，在弹出的快捷菜单中选择“启用”，打开“启用触发器”对话框。步骤02：“启用触发器”对话框将显示启用的结果及相关信息，如图10-7所示。单击“关闭”按钮，实现对“student_tri”触发器的启用。10.2 管理DML触发器4.删除触发器如果触发器已经彻底失去作用，可以将其删除。步骤01：在SSMS中依次展开“student”“student_table”“触发器”节点，右击触发器“student_t
20、ri”子节点，在弹出的快捷菜单中选择“删除”，打开“删除对象”对话框。步骤02：单击“确定”按钮，实现对“student_tri”触发器的删除，如图10-8所示。10.2 管理DML触发器【相关知【相关知识】1.1.触触发器的修改器的修改触触发器的修改器的修改语句与句与创建建语句基本一致，具体如下：句基本一致，具体如下：ALTER TRIGGER 触发器名ON 表名|视图名WITH ENCRYPTIONFOR DELETE ,INSERT ,UPDATE AS IF UPDATE(字段名).AND|OR UPDATE(字段名).N sql_statement .N各参数的含义与CREATETR
21、IGGER语句中所使用的参数相同，这里不在赘述。10.2 管理DML触发器2.触触发发器的禁用与启用器的禁用与启用因因为为触触发发器在使用的器在使用的过过程中采用的是自程中采用的是自动执动执行方式，不需要人工指定，所以如果不需要触行方式，不需要人工指定，所以如果不需要触发发器器发挥发挥作用，只能将其作用，只能将其临时临时禁用。禁用并不是禁用。禁用并不是删删除，只是除，只是暂时暂时禁止其禁止其发挥发挥作用。作用。禁用后，如果需要恢复其作用，重新启用即可。禁用后，如果需要恢复其作用，重新启用即可。【例【例10-2】管理触】管理触发发器器本本实实例例实现对实现对【例【例10-1】中】中创创建的建的“
22、DEL_tri”触触发发器的管理。器的管理。将触将触发发器内提示用器内提示用户户的信息改的信息改为为“用用户户您好，您您好，您刚刚才才删删除的数据信息除的数据信息为为：”。并完成。并完成该该触触发发器的禁用、启用和器的禁用、启用和删删除操作。除操作。步步骤骤01：触：触发发器的修改。在器的修改。在查询编辑查询编辑器中器中输输入如下入如下语语句：句：ALTER TRIGGER DEL_triON student_tableFOR DELETEASprint用户您好，您刚才删除的数据信息为：SELECT*FROM deleted步骤步骤0202：触发器的禁用。在SSMS中，右击触发器“DEL_tr
23、i”节点，在弹出的快捷菜单中选择“禁用”，弹出“禁用触发器”对话框，单击“关闭”按钮，完成禁用操作。步骤步骤0303：触发器的启用。在SSMS中，右击触发器“DEL_tri”节点，在弹出的快捷菜单中选择“启用”，弹出“启用触发器”对话框，单击“关闭”按钮，完成启用操作。步骤步骤0404：触发器的删除。在SSMS中，右击触发器“DEL_tri”节点，在弹出的快捷菜单中选择“删除”，弹出“删除对象”对话框，单击“确定”按钮，完成删除操作。任务10.3 创建及管理DDL触发器10.3 创建及管理DDL触发器【任务描述】DML触发器是针对数据库中的操作语言进行触发，DDL触发器则是针对数据库中的定义语
24、言进行触发。本任务将对student数据库创建一个DDL触发器，以实现数据库的保护。【任务分析】实际上，DDL触发器和DML触发器只是功能不同而已，其原理和创建过程基本一样。完成该任务需要做到以下几点：（1）创建DDL触发器。（2）查看与修改DDL触发器。（3）禁用、启用与删除DDL触发器。10.3 创建及管理DDL触发器【任务实现】1.创建DDL触发器。步骤01：在SSMS中依次展开“student”|“表”“student_table”节点，右击“触发器”子节点，在弹出的快捷菜单中选择“新建触发器”，打开触发器编辑窗口。步骤02：与创建DML触发器一样，在触发器编辑窗口中输入如下语句创建触
25、发器。CREATE TRIGGER DATABASE_Trig -DATABASE_TRIG为触发器名ONALLSERVER-针对所有服务器FORDROP_DATABASE-针对删除数据库操作ASPRINT 数据库已使用触发器保护，不能删除 -输出的提示信息ROLLBACK-数据回滚，取消删除操作10.3 创建及管理DDL触发器步骤03：输入如下删除数据库语句，验证DDL触发器的有效性。DROP DataBase student此时，触发器被激活，拒绝本次操作，并给出相应提示。10.3 创建及管理DDL触发器2.2.触发器的查看与修改。触发器的查看与修改。步骤步骤0101：DDL触发器与DML
26、触发器的查看方式不同。在SSMS中依次展开“服务器对象”“触发器”节点，右击触发器“DATABASE_TRIG”子节点，在弹出的快捷菜单中选择“编写服务器触发器脚本为”“CREATE到”“新查询编辑器窗口”，如图10-9所示。步骤步骤0202：查看。在打开的窗口中可以查看该触发器创建时的语句。步骤步骤0303：修改。与DML触发器一样，首先将语句中的“CREATETRIGGER”语句改为“ALTERTRIGGER”语句，然后在下面的语句中进行相应修改即可。步骤步骤0404：删除DDL触发器。在SSMS中依次展开“服务器对象”“触发器”节点，右击触发器“DATABASE_TRIG”子节点，在弹出
27、的快捷菜单中选择“删除”，打开“删除对象”对话框，单击“确定”按钮，完成删除。10.3 创建及管理DDL触发器【相关知【相关知识】1.DDL1.DDL触触发器的器的创建建语法法DDLDDL触触发器的器的创建建语法如下：法如下：CREATETRIGGERONALLSERVER|DATABASEFOR|AFTERASsql语句参数说明如下：CREATE TRIGGER：新建触发器语句。trigger_name：触发器名称。ON ALL SERVER|DATABASE：触发器针对的对象，ALL SERVER表示针对所有服务器，DataBase则表示针对数据库。FOR|AFTER：AFTER 用于指定
28、DML触发器仅在SQL语句指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。如果仅指定FOR关键字，则AFTER为默认值。不能对视图定义AFTER触发器。AS sql语句：要执行的SQL语句。10.3 创建及管理DDL触发器【例【例10-310-3】管理】管理DDLDDL触触发器器本本实例要求保例要求保护数据表功能。数据表功能。创建两个触建两个触发器，拒器，拒绝用用户对数据表数据表student_tablestudent_table的修改及在的修改及在studentstudent数据数据库中中创建新的建新的数据表。数据表。步步骤0101：拒：拒
29、绝修改数据表。打开修改数据表。打开SSMSSSMS，将当前数据，将当前数据库设定定为studentstudent，在，在查询编辑器中器中输入如下入如下语句：句：CREATETRIGGERTABLE_TRIG1ONDATABASEFORALTER_TABLEASPRINT数据表已使用触发器保护，不能修改ROLLBACK触发器创建好后，如果用户要修改表，系统会给出拒绝操作提示。步骤02：打开SSMS，将当前数据库设定为student，在查询编辑器中输入如下语句：CREATE TRIGGER TABLE_TRIG2ON DATABASEAFTER DDL_TABLE_EVENTSAS PRINT 此数据库已保护，不能在其中创建表ROLLBACK;触发器创建好后，如果用户要创建表，系统会给出拒绝操作提示。