第7章存储过程和触发器课件.ppt

《第7章存储过程和触发器课件.ppt》由会员分享，可在线阅读，更多相关《第7章存储过程和触发器课件.ppt（58页珍藏版）》请在三一办公上搜索。

1、第7章 存储过程和触发器,存 储 过 程,触 发 器,7 . 1 存 储 过 程,使用存储过程的优点如下：（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后，就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。（3）使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数据库信息访问的权限，确保数据库的安全。（4）自动完成需要预先执行的任务。存储过程可以在SQL Server启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。,7.1.1 存储过程的类型,在SQL

2、 Server 2008中有下列几种类型的存储过程。（1）系统存储过程。系统存储过程是由SQL Server提供的存储过程，可以作为命令执行。系统存储过程定义在系统数据库master中，其前缀是“sp_”，例如，常用的显示系统对象信息的sp_help系统存储过程，为检索系统表的信息提供了方便快捷的方法。（2）扩展存储过程。扩展存储过程是指在SQL Server 2008环境之外，使用编程语言（如C+语言）创建的外部例程形成的动态链接库（DLL）。使用时，先将DLL加载到SQL Server 2008系统中，并且按照使用系统存储过程的方法执行。扩展存储过程在 SQL Server 实例地址空间中

3、运行。但因为扩展存储过程不易撰写，而且可能会引发安全性问题，所以微软可能会在未来的SQL Server中删除这一功能，本书将不详细介绍扩展存储过程。（3）用户存储过程。在SQL Server 2008中，用户存储过程可以使用T-SQL语言编写，也可以使用CLR方式编写。在本书中，T-SQL存储过程就称为存储过程。,7.1.2 存储过程的创建与执行,1使用命令方式创建存储过程创建存储过程的语句是CREATE PROCEDURE或CREATE PROC，两者同义。语法格式：CREATE PROC | PROCEDURE 架构名. 过程名 ; 组号 /*定义过程名*/ 参数 类型架构名. 数据类型

4、/*定义参数的类型*/ VARYING = default OUT | OUTPUT READONLY/*定义参数的属性*/ , . WITH , . /*定义存储过程的处理方式*/ FOR REPLICATION AS ; . /*执行的操作*/ | EXTERNAL NAME 程序集名.类名.方法名 ; ,7.1.2 存储过程的创建与执行,1）命令主体CREATE PROCEDURE命令主体结构说明如下：（1）过程名：用于指定存储过程名，必须符合标识符规则，且对于数据库及所在架构必须唯一。（2）;组号：为可选的整数，用于对同名的存储过程进行分组，以便使用一条DROP PROCEDURE语句

5、就可删除一组存储过程（3）参数：为存储过程的形参，符号作为第一个字符来指定参数名称。（4）数据类型：用于指定形参的数据类型，形参可为SQL Server 2008支持的任何类型，但cursor类型只能用于OUTPUT参数，如果指定参数的数据类型为cursor，则必须同时指定VARYING和OUTPUT关键字，OUT与OUTPUT关键字意义相同。（5）VARYING：指定作为输出参数支持的结果集。,7.1.2 存储过程的创建与执行,（6）default：指定存储过程输入参数的默认值，默认值必须是常量或NULL。（7）OUTPUT：指示参数为输出参数，输出参数可以从存储过程返回信息。（8）READ

6、ONLY：指定不能在存储过程的主体中更新或修改参数。（9）：用于定义存储过程的处理方式。（10）FOR REPLICATION：用于说明不能在订阅服务器上执行为复制创建的存储过程，如果指定了FOR REPLICATION，则无法声明参数。（11）SQL语句：代表过程体包含的T-SQL语句，存储过程体中可以包含一条或多条T-SQL语句，除了DCL、DML与DDL命令外，还能包含过程式语句，如变量的定义与赋值、流程控制语句等。（12）EXTERNAL NAME：指定.NET Framework程序集的方法，以便CLR存储过程引用。,7.1.2 存储过程的创建与执行,2）过程选项的具体格式为： :=

7、 ENCRYPTION RECOMPILE EXECUTE AS 子句 （1）ENCRYPTION：指定SQL Server对CREATE PROCEDURE语句的原始文本进行加密。对于CLR存储过程，这个选项不可用。（2）RECOMPILE：指定数据库引擎不缓存该过程的计划，该过程在运行时编译。（3）EXECUTE AS子句：指定在其中执行存储过程的安全上下文。,7.1.2 存储过程的创建与执行,3）注意事项（1）用户定义的存储过程只能在当前数据库中创建（临时存储过程除外，它总是在系统数据库tempdb中创建）。存储过程名称存储在sysobjects系统表中，而语句的文本存储在syscomm

8、ents中。（2）SQL Server启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在master数据库中创建，并在sysadmin固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。 （3）CREATE PROCEDURE的权限默认授予sysadmin固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将CREATE PROCEDURE权限转让给其他用户。,7.1.2 存储过程的创建与执行,（4）SQL语句的限制。 如下语句必须使用对象的架构名对数据

9、库对象进行限定：CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS及DBCC语句。 如下语句不能出现在CREATE PROCEDURE定义中：SETPARSEONLY、SETSHOWPLAN_TEXT、SETSHOWPLAN_XML、SETSHOWPLAN_ALL、CREATESCHEMA、CREATE FUNCTION、ALTER FUNCTION、CREATE PROCEDURE、ALTER PROCEDURE、CREATE TRIGGER、ALTER TR

10、IGGER、CREATE VIEW、ALTER VIEW、USE 数据库名等。,7.1.2 存储过程的创建与执行,2存储过程的执行通过EXECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是EXECUTE的简写。语法格式： EXEC | EXECUTE 返回状态 = 模块名 ;组号 | 模块名变量 参数名 = 值| 变量 OUTPUT | DEFAULT , . WITH RECOMPILE ; ,7.1.2 存储过程的创建与执行,1）语句说明（1）返回状态：为可选的整型变量，保存存储过程的返回状态。EXECUTE语句使用该变量前，必须对其声明。（2）模块名：要调用的存储过程或用户

11、定义标量函数的完全限定或者不完全限定名称。“组号”用于调用已定义的一组存储过程中的某一个。（3）模块名变量：局部定义的变量名，保存存储过程或用户定义函数的名称。（4）参数名：为CREATE PROCEDURE或CREATE FUNCTION语句中定义的参数名，“值”为实参。如果省略“参数名”，则后面的实参顺序要与定义时参数的顺序一致。（5）变量：为局部变量，用于保存OUTPUT参数返回的值。（6）DEFAULT：DEFAULT关键字表示不提供实参，而是使用对应的默认值。（7）WITH RECOMPILE：执行模块后，强制编译、使用和放弃新计划。,7.1.2 存储过程的创建与执行,2）注意事项存

12、储过程的执行要注意以下几点：（1）如果存储过程名的前缀为“sp_”，SQL Server会首先在master数据库中寻找符合该名称的系统存储过程。如果没能找到合法的过程名，SQL Server才会寻找架构名称为dbo的存储过程。（2）在执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE关键字。,7.1.2 存储过程的创建与执行,3）举例（1）设计简单的存储过程。【例7.1】 返回081101号学生的成绩情况。该存储过程不使用任何参数。USE PXSCJGOCREATE PROCEDURE student_info AS SELECT * FROM CJB WHERE 学

13、号= 081101GO存储过程定义后，执行存储过程student_info：EXECUTE student_info如果该存储过程是批处理中的第一条语句，则可使用：student_info执行结果如图7.1所示。,7.1.2 存储过程的创建与执行,（2）使用带参数的存储过程。【例7.2】 从PXSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接收与传递参数精确匹配的值。USE PXSCJGOCREATE PROCEDURE student_info1 name char (8), cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 F

14、ROM XSB a INNER JOIN CJB b ON a.学号 = b.学号 INNER JOIN KCB t ON b.课程号= t.课程号 WHERE a.姓名=name and t.课程名=cnameGO,7.1.2 存储过程的创建与执行,执行存储过程student_info1：EXECUTE student_info1 王林, 计算机基础执行结果如图7.2所示。,以下命令的执行结果与上面的相同：EXECUTE student_info1 name=王林, cname=计算机基础或者：DECLARE proc char(20)SET proc= student_info1EXECU

15、TE proc name=王林, cname=计算机基础,7.1.2 存储过程的创建与执行,（3）使用带OUPUT参数的存储过程。【例7.3】 创建一个存储过程do_insert，作用是向XSB表中插入一行数据。创建另外一个存储过程do_action，在其中调用第一个存储过程，并根据条件处理该行数据，处理后输出相应的信息。第一个存储过程：CREATE PROCEDURE dbo.do_insert AS INSERT INTO XSB VALUES(091201, 陶伟, 1, 1990-03-05, 软件工程,50, NULL);,7.1.2 存储过程的创建与执行,第二个存储过程：CREAT

16、E PROCEDURE do_action X bit, STR CHAR(8) OUTPUT AS BEGIN EXEC do_insert IF X=0 BEGIN UPDATE XSB SET 姓名=刘英, 性别=0 WHERE 学号=091201 SET STR=修改成功 END ELSE IF X=1 BEGIN DELETE FROM XSB WHERE 学号=091201 SET STR=删除成功 END END,7.1.2 存储过程的创建与执行,接下来执行存储过程do_action来查看结果：DECLARE str char(8)EXEC dbo.do_action 0, st

17、r OUTPUTSELECT str;执行结果如图7.3所示。,7.1.2 存储过程的创建与执行,（4）使用带有通配符参数的存储过程。【例7.4】 从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。CREATE PROCEDURE st_info name varchar(30) = 李% AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XSB a INNER JOIN CJB b ON a.学号 =b.学号 INNER JOIN KCB c ON c.课程号= b.课程号 WH

18、ERE 姓名 LIKE nameGO 执行存储过程：EXECUTE st_info /*参数使用默认值*/或者:EXECUTE st_info 王% /*传递给name 的实参为王%*/,7.1.2 存储过程的创建与执行,（5）使用OUTPUT游标参数的存储过程。OUTPUT游标参数用于返回存储过程的局部游标。【例7.5】 在 PXSCJ数据库的XSB表上声明并打开一个游标。CREATE PROCEDURE st_cursor st_cursor cursor VARYING OUTPUT AS SET st_cursor = CURSOR FORWARD_ONLY STATIC FOR SE

19、LECT * FROM XSB OPEN st_cursor,7.1.2 存储过程的创建与执行,在如下的批处理中，声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录。DECLARE MyCursor cursorEXEC st_cursor st_cursor = MyCursor OUTPUT/*执行存储过程*/FETCH NEXT FROM MyCursorWHILE (FETCH_STATUS = 0)BEGIN FETCH NEXT FROM MyCursorENDCLOSE MyCursorDEALLOCATE MyCursor,7.1.2

20、 存储过程的创建与执行,（6）使用WITH ENCRYPTION选项。WITH ENCRYPTION子句用于对用户隐藏存储过程的文本。【例7.6】 创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM XSB通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。执行如下语句：EXEC sp_helptext encrypt_

21、this结果集为提示信息“对象encrypt_this的文本已加密”。,7.1.3 存储过程的修改,使用ALTER PROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。语法格式：ALTER PROC | PROCEDURE 架构名. 过程名 ; 组号 参数 类型架构名. 数据类型 VARYING = default OUTPUT , . WITH , . FOR REPLICATION AS ; . | EXTERNAL NAME 程序集名.类名.方法名 ; ,7.1.3 存储过程的修改,【例7.7】 对例7.2中创建的存储过程student_info1进行修改，将第一个参数改成

22、学生的学号。USE PXSCJGOALTER PROCEDURE student_info1 number char(6),cname char(16) AS SELECT 学号, 课程名, 成绩 FROM CJB, KCB WHERE CJB.学号=number AND KCB.课程名=cnameGO,7.1.3 存储过程的修改,【例7.8】 创建名为select_students的存储过程，在默认情况下，该存储过程可查询所有学生信息，随后授予权限。当该存储过程需更改为能检索计算机专业的学生信息时，用ALTER PROCEDURE命令重新定义该存储过程。创建select_students存储

23、过程：CREATE PROCEDURE select_students /*创建存储过程*/ AS SELECT * FROM XSB ORDER BY 学号GO修改存储过程select_students：ALTER PROCEDURE select_students WITH ENCRYPTION AS SELECT * FROM XSB WHERE 专业= 计算机 ORDER BY 学号GO,7.1.4 存储过程的删除,当不再使用一个存储过程时，就要把它从数据库中删除。使用DROP PROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：DROP

24、 PROC | PROCEDURE 架构名. 过程 , . 说明：过程是指要删除的存储过程或存储过程组的名称。USE PXSCJGOIF EXISTS(SELECT name FROM sysobjects WHERE name=student_info) DROP PROCEDURE student_info,7.1.5 界面方式操作存储过程,1创建存储过程例如，如果要通过图形向导方式定义一个存储过程来查询PXSCJ数据库中每个同学各门功课的成绩，那么其主要步骤如下：启动“SQL Server Management Studio”，在“对象资源管理器”中展开“数据库”“PXSCJ”，选择其中

25、的“可编程性”，右击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”菜单项，打开“存储过程脚本编辑”窗口，如图7.4所示。,7.1.5 界面方式操作存储过程,2执行存储过程在PXSCJ数据库的“存储过程”目录下选择要执行的存储过程，如student_info1，右击鼠标，选择“执行存储过程”菜单项。在弹出的“执行过程”窗口中会列出存储过程的参数形式，如果“输出参数”栏为“否”，则表示该参数为输入参数，用户需要设置输入参数的值，在“值”一栏中输入即可，如图7.5所示。,7.1.5 界面方式操作存储过程,3修改存储过程在“存储过程”目录下选择要修改的存储过程，右击鼠标，在弹出的快捷菜单中选择“

26、修改”菜单项，打开“存储过程脚本编辑”窗口，在该窗口中修改相关的T-SQL语句。修改完成后，执行修改后的脚本，若执行成功，则修改了存储过程。4删除存储过程选择要删除的存储过程，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，根据提示删除该存储过程。,7 . 2 触 发 器,7.2.1 触发器的类型1DML触发器当数据库中发生数据操纵语言（DML）事件时将调用DML触发器。一般情况下，DML事件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因而DML触发器也可分为三种类型：INSERT、UPDATE和DELETE。利用DML触发器可以方便地保持数据库中数据的完整性。例如，

27、对于PXSCJ数据库有XSB表、CJB表和KCB表，当插入某一学号的学生某一课程的成绩时，该学号应是XSB表中已存在的，课程号应是KCB表中已存在的，此时，可通过定义INSERT触发器实现上述功能。通过DML触发器可以实现多个表间数据的一致性。,7.2.1 触发器的类型,2DDL触发器DDL触发器也是由相应的事件触发的，但DDL触发器触发的事件是数据定义语句（DDL）。这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。DDL触发器的主要作用是执行管理操作，如审核系统、控制数据库的操作等。通常情况下，DDL触发器主要用于以下一些操作需求：防止对数据库架构进行某些修改；希望数据

28、库中发生某些变化以利于相应数据库架构中的更改；记录数据库架构中的更改或事件。DDL触发器只在响应由T-SQL语法所指定的DDL事件时才会触发。,7.2.2 触发器的创建,1创建DML触发器语法格式：CREATE TRIGGER 架构名. 触发器名 ON 表 | 视图 /*指定操作对象*/ WITH ENCRYPTION /*说明是否采用加密方式*/ FOR |AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION /*说明该触发器不用于复制*/AS SQL语句 ; . | EXTERNAL NAME

29、 程序集名.类名.方法名,7.2.2 触发器的创建,1）语句说明（1）触发器名：用于指定触发器名，触发器名必须符合标识符规则，并且在数据库中必须唯一。“架构名”是DML触发器所属架构的名称。对于DDL触发器，无法指定架构名。（2）表 | 视图：指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。（3）AFTER：用于说明触发器在指定操作都成功执行后触发，如AFTER INSERT表示向表中插入数据时激活触发器。不能在视图上定义AFTER触发器。（4）INSTEAD OF：指定用DML触发器中的操作代替触发语句的操作。（5） INSERT , UPDATE , DELETE ：指定激活触

30、发器的语句的类型，必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。（6）WITH APPEND：指定应该再添加一个现有类型的触发器。（7）SQL语句：触发器的T-SQL语句，可以有一条或多条语句，指定DML触发器触发后将要执行的动作。,7.2.2 触发器的创建,2）触发器说明触发器有以下几点说明：（1）触发器中使用的特殊表。执行触发器时，系统创建了两个特殊的临时表inserted表和deleted表，下面介绍一下这两个表的内容。inserted表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。deleted表：用于保存已从表中

31、删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted表中。,7.2.2 触发器的创建,（2）创建DML触发器的说明。创建DML触发器时主要有以下几点说明： CREATE TRIGGER 语句必须是批处理中的第一条语句，并且只能应用到一个表中。 DML触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象。 创建DML触发器的权限默认分配给表的所有者。 在同一CREATE TRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。 不能对临时表或系统表创建DML触发器。 对于含有DELETE或UPDATE操作定义的外键表，不能使用I

32、NSTEAD OF DELETE和INSTEAD OF UPDATE触发器。,7.2.2 触发器的创建, TRUNCATE TABLE语句虽然能够删除表中的记录，但它不会触发DELETE触发器。 在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。 DML触发器最大的用途是返回行级数据的完整性，而不是返回结果，所以应当尽量避免返回任何结果集。 CREATE TRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。 DML触发器中不能包

33、含以下语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。,7.2.2 触发器的创建,（3）创建INSERT触发器。INSERT触发器是当对触发器表执行INSERT语句时就会激活的触发器。INSERT触发器可以用来修改，甚至拒绝接收正在插入的记录。【例7.10】 创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并显示。USE PXSCJ

34、GOCREATE TABLE table1(a int)GOCREATE TRIGGER table1_insert ON table1 AFTER INSERT AS BEGIN DECLARE str char(50) SET str=TRIGGER IS WORKING PRINT str END,7.2.2 触发器的创建,向table1中插入一行数据：INSERT INTO table1 VALUES(10)执行结果如图7.6所示。,7.2.2 触发器的创建,【例7.11】 创建触发器，当向CJB表中插入一个学生的成绩时，将XSB表中该学生的总学分加上添加的课程的学分。CREATE T

35、RIGGER cjb_insert ON CJB AFTER INSERT AS BEGIN DECLARE num char(6), kc_num char(3) DECLARE xf int SELECT num=学号, kc_num=课程号 from inserted SELECT xf=学分 FROM KCB WHERE 课程号=kc_num UPDATE XSB SET 总学分=总学分+xf WHERE 学号=num PRINT 修改成功 END,7.2.2 触发器的创建,（4）创建UPDATE触发器。UPDATE触发器在对触发器表执行UPDATE语句后触发。在执行UPDATE触发器

36、时，将触发器表的原记录保存到deleted临时表中，将修改后的记录保存到inserted临时表中。【例7.12】 创建触发器，当修改XSB表中的学号时，同时也要将CJB表中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。CREATE TRIGGER xsb_update ON XSB AFTER UPDATE AS BEGIN DECLARE old_num char(6), new_num char(6) SELECT old_num=学号 FROM deleted SELECT new_num=学号 FROM inserted UPDATE CJB SET 学号=ne

37、w_num WHERE 学号=old_num END,7.2.2 触发器的创建,接着修改XSB表中的一行数据，并查看触发器执行结果：UPDATE XSB SET 学号=081120 WHERE 学号=081101GOSELECT * FROM CJB WHERE 学号=081120执行结果如图7.7所示。,7.2.2 触发器的创建,（5）创建DELETE触发器。【例7.13】 在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。CREATE TRIGGER xsb_delete ON XSB AFTER DELETE AS BEGIN DELETE FROM CJB WHERE

38、 学号 IN(SELECT 学号 FROM deleted) END,7.2.2 触发器的创建,【例7.14】 在KCB表中创建UPDATE和DELETE触发器，当修改或删除KCB表中的课程号字段时，同时修改或删除CJB表中的该课程号。CREATE TRIGGER kcb_trig ON KCB AFTER UPDATE, DELETE AS BEGIN IF (UPDATE(课程号) UPDATE CJB SET 课程号=(SELECT 课程号 FROM inserted) WHERE 课程号=(SELECT 课程号 FROM deleted) ELSE DELETE FROM CJB WH

39、ERE 课程号 IN(SELECT 课程号 FROM deleted) END,7.2.2 触发器的创建,（6）创建INSTEAD OF触发器。AFTER触发器是在触发语句执行后触发的，与AFTER触发器不同的是，INSTEAD OF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。一个表或视图中只能有一个INSTEAD OF触发器。【例7.15】 创建表table2，值包含一列a，在表中创建INSTEAD OF INSERT触发器，当向表中插入记录时显示相应消息。USE PXSCJGOCREATE TABLE table2(a int)GOCREATE TRIGGE

40、R table2_insert ON table2 INSTEAD OF INSERT AS PRINT INSTEAD OF TRIGGER IS WORKING向表中插入一行数据：INSERT INTO table2 VALUES(10)执行结果如图7.8所示。,7.2.2 触发器的创建,【例7.16】 在PXSCJ数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。创建视图：CREATE VIEW st

41、u_viewAS SELECT XSB.学号, 专业, 课程号, 成绩 FROM XSB, CJB WHERE XSB.学号=CJB.学号,7.2.2 触发器的创建,创建INSTEAD OF触发器：CREATE TRIGGER InsteadTrig ON stu_view INSTEAD OF INSERT AS BEGIN DECLARE XH char(6), XM char(8), ZY char(12), KCH char(3), CJ int SET XM=佚名 SELECT XH=学号, ZY=专业, KCH=课程号, CJ=成绩 FROM inserted INSERT INT

42、O XSB(学号, 姓名, 专业) VALUES(XH, XM, ZY) INSERT INTO CJB VALUES(XH, KCH, CJ) END,7.2.2 触发器的创建,向视图插入一行数据：INSERT INTO stu_view VALUES(091102, 计算机, 101, 85 )查看数据是否插入：SELECT * FROM stu_view WHERE 学号= 091102执行结果如图7.9所示。,7.2.2 触发器的创建,查看与视图关联的XSB表的情况：SELECT * FROM XSB WHERE 学号= 091102执行结果如图7.10所示。,7.2.2 触发器的创建

43、,2创建DDL触发器语法格式：CREATE TRIGGER 触发器名 ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER 事件类型 | 事件组 , . AS SQL语句 ; . | EXTERNAL NAME 程序集名.类名.方法名,7.2.2 触发器的创建,【例7.17】 创建PXSCJ数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。USE PXSCJGOCREATE TRIGGER safety ON DATABASE AFTER DROP_TABLE AS PRINT 不能删除该表 ROLLBACK

44、TRANSACTION尝试删除表table1：DROP TABLE table1执行结果如图7.11所示。,7.2.2 触发器的创建,【例7.18】 创建服务器作用域的DDL触发器，当删除一个数据库时，提示禁止该操作并回滚删除数据库的操作。CREATE TRIGGER safety_server ON ALL SERVER AFTER DROP_DATABASE AS PRINT 不能删除该数据库 ROLLBACK TRANSACTION,7.2.3 触发器的修改,要修改触发器执行的操作，可以使用ALTER TRIGGER语句。（1）修改DML触发器的语法格式：ALTER TRIGGER 架构

45、名.触发器名 ON ( 表 | 视图 ) WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLICATION AS SQL语句 ; . | EXTERNAL NAME 程序集名.类名.方法名,7.2.3 触发器的修改,（2）修改DDL触发器的语法格式：ALTER TRIGGER 触发器名 ON DATABASE | ALL SERVER WITH ENCRYPTION FOR | AFTER 事件类型 , . | 事件组 AS SQL语句 ; | EXTERNAL NAME 程序集名

46、.类名.方法名 ; ,7.2.3 触发器的修改,【例7.19】 修改PXSCJ数据库中在XSB表上定义的触发器xsb_delete，将其修改为UPDATE触发器。USE PXSCJGOALTER TRIGGER xsb_delete ON XSB FOR UPDATE AS PRINT 执行的操作是修改,7.2.4 触发器的删除,触发器本身是存在于表中的，因此，当表被删除时，表中的触发器也将一起被删除。删除触发器使用DROP TRIGGER语句。语法格式：DROP TRIGGER 架构名.触发器名 ,. ; /*删除DML触发器*/DROP TRIGGER 触发器名 ,. ON DATABAS

47、E | ALL SERVER ; /*删除DDL触发器*/【例7.20】 删除DML触发器xsb_delete。IF EXISTS (SELECT name FROM sysobjects WHERE name = xsb_delete) DROP TRIGGER xsb_delete【例7.21】 删除DDL触发器safety。DROP TRIGGER safety ON DATABASE,7.2.5 界面方式操作触发器,1创建触发器（1）通过界面方式只能创建DML触发器。以在表XSB上创建触发器为例，利用“对象资源管理器”创建DML触发器的步骤如下：启动“SQLServerManageme

48、ntStudio”，在“对象资源管理器”中展开“数据库”“PXSCJ”“表”“dbo.XSB”，选择其中的“触发器”目录，在该目录下可以看到之前已经创建的XSB表的触发器。右击“触发器”，在弹出的快捷菜单中选择“新建触发器”菜单项。在打开的“触发器脚本编辑”窗口输入相应的创建触发器的命令。输入完成后，单击“执行”按钮，若执行成功，则触发器创建完成。（2）查看DDL触发器。DDL触发器不可以使用界面方式创建，DDL触发器分为数据库触发器和服务器触发器，展开“数据库”“PXSCJ”“可编程性”“数据库触发器”，可以查看到有哪些数据库触发器。展开“数据库”“服务器对象”“触发器”，可以查看到有哪些服

49、务器触发器。,7.2.5 界面方式操作触发器,2修改触发器DML触发器能够使用界面方式修改，DDL触发器则不可以。进入“对象资源管理器”，修改触发器的步骤与创建的步骤相同，在“对象资源管理器”中选择要修改的触发器，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“触发器脚本编辑”窗口，在该窗口中可以进行触发器的修改，修改后单击“执行”按钮重新执行即可。但是被设置成“WITH ENCRYPTION”的触发器是不能被修改的。,7.2.5 界面方式操作触发器,3删除触发器（1）删除DML触发器。以XSB表的DML触发器为例，在“对象资源管理器”中展开“数据库”“PXSCJ”“表”“dbo.XSB”“触发器”，选择要删除的触发器名称，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，在弹出的“删除对象”窗口中单击“确定”按钮，即可完成触发器的删除操作。（2）删除DDL触发器。删除DDL触发器与删除DML触发器的方法类似，首先找到要删除的触发器，右击鼠标，选择“删除”选项即可。,