数据库技术与应用第5章(Transact-SQL、存储过程等).ppt

数据库技术及应用DB 本章主要内容: T-SQL的基本语法 存储过程(难点) 触发器(难点) 第5章 Transact-SQL、 存储过程和触发器 1 5.1 Transact-SQL语言5.1.1 数据类型 数据类型见表5-1。下面做一些必要的说明。 1. 整型(Bigint、Int、Smallint、tinyint) 整型数参与任何算术运算的结果只保留数值的整数部 分。 DECLARE n1 int , n2 int , n3 int -变量的声明 SET n1=14 SET n2=3 SET n3=n1/n2 -n3=4 SELECT n1 AS n1, n2 AS n2 , n3 AS n3 -起别名 2 5.1.1 数据类型 2. 浮点型(float、real 、Decimal、Numerc) float和real属于近似数据类型。特点是表示范 围大,但任何一个数都仅是一个近似值。适合用 于科学计算。 Decimal和Numerc是精确数值类型，表示的是 一个精确值。适合用于财务金融（如：工资） 。 5.1.1 数据类型 3 5.1.1 数据类型 Decimal和Numeric的表示范围和精度及所占内 存都一样，为与ANSI标准兼容SQL Server支持 这两种表示。 Decimal(p , s ): p表示总位数(不包括小数点)， 介于138之间，默认值为18。 s表示小数点后的 位数，默认值是0。参数间关系：00) PRINT 有事务在运行 ELSE PRINT 无事务在运行 19 3. 运算符 运算符的类型： 算术运算符 比较运算符 字符串连接运算符 逻辑运算符 5.1.2 标识符、变量和运算符 5.1.2 标识符、变量和运算符 20 (1) 算术运算符：+、-、*、/、%(求余) (2) 比较运算符: =、=、 5.1.2 标识符、变量和运算符 (3) 字符串连接运算符：。 例如： print asd + dfg + 1234 结果为： asddfg1234 空串( )作为单个空格处理。 例如： abc + + efg ，结果为abc egf . 5.1.2 标识符、变量和运算符 21 5.1.2 标识符、变量和运算符 运算符含义 ALL(集合)如果一系列的比较都为True，则为 True。 AND逻辑与 ANY(集合)如果一系列的比较中任何一个为True ，则为True。 BETWEEN如果操作数在某个范围之内，则为真 (4)逻辑运算符（见表5-6 ） 5.1.2 标识符、变量和运算符 22 5.1.2 标识符、变量和运算符 运算符含义 EXISTS如果子查询不为空，则为True IN(集合)如果操作数在集合中，则为真 LIKE如果操作数与某个字符串匹配，则为真 NOT逻辑非 OR逻辑或 SOME与ANY一样 表5-6 (续) 5.1.2 标识符、变量和运算符 23 (5) 运算符优先级 括号算术运算符(字符串连接符) 比较运算符逻辑运算符赋值（由高到 低） 5.1.2 标识符、变量和运算符 5.1.2 标识符、变量和运算符 24 SQL Server提供了非常丰富的函数供用户使 用，同时也允许用户定义自己的函数。 5.1.3 函数 查询分析器中可 以查看到系统提 供的各种函数。 5.1.3 函数 25 1. Transact-SQL提供的系统函数有： 5.1.3 函数 用户自定义函数 5.1.3 函数 26 函数分为三大类： 行集函数（也称表值函数） 可以用在SQL语句中“表”出现的任何地方引 用行集函数。 聚合函数 (第4 章介绍过)：也称计合函数。 5.1.3 函数 标量值函数： 最常用的一类函数。通过有参或无参调用它， 可以使其执行并返回单个值。 5.1.3 函数 27 5.1.3 函数 系统提供的函数，参考教材。不再例举。 5.1.3 函数 28 5.1.3 函数 2. 用户自定义函数 用户可以根据应用需要定义自己的函数。 自定义函数分三种：标量函数、内嵌表值函 数、多语句表值函数。下面我们介绍前2种。 (1) 标量函数 标量函数指函数返回单个值(字符串数值等). 语法格式： 见下页。 5.1.3 函数 29 5.1.3 函数 CREATE FUNCTION 函数名 （形式参数定义表） RETURNS 函数返回值数据类型 AS BEGIN 函数体 RETURN 返回值表达式 END 5.1.3 函数 30 5.1.3 函数 例3 创建一个标量函数，返回某学生的平均分 数。 学号作为函数参数。 5.1.3 函数 CREATE FUNCTION get_avg ( sno char(6) ) RETURNS int AS BEGIN DECLARE temp int SELECT temp =AVG(成绩) FROM 选课 WHERE 学号= sno RETURN temp END 31 5.1.3 函数 可以用下列语句调用get_avg函数： SELECT dbo.get_avg(030101) AS 030101平均成绩 注意：调用时必须给出用户名dbo。它是函数 创建者。 (2) 内嵌表值函数 内嵌表值函数指 函数返回值是一个表. 语法格式：见下页。 5.1.3 函数 32 5.1.3 函数 CREATE FUNCTION 函数名 （形式参数定义表） RETURNS TABLE AS RETURN ( SELECT查询语句 ) 函数返回值是一个查询表。 5.1.3 函数 33 5.1.3 函数 例4 创建一个表值函数，返回平均分数大于或等于指 定分数的学生学号和平均分数。函数参数为给定的一 个“分数”。 5.1.3 函数 CREATE FUNCTION get_all_avg ( score int ) RETURNS TABLE AS RETURN SELECT 学号, AVG(成绩) AS 平均成绩 FROM 选课 GROUP BY 学号 HAVING AVG(成绩)= score 注意：表达式AVG(成绩) 必须给出别名。 34 5.1.3 函数 函数的调用：查询平均成绩大于80分的学生信息。 SELECT 学生.学号, 姓名 , 平均成绩 FROM get_all_avg(80) , 学生 WHERE get_all_avg.学号 = 学生.学号 5.1.3 函数 35 5.1.3 函数 (3) 删除用户自定义函数 DROP FUNCTION 函数名 (4) 修改用户自定义函数 ALTER FUNCTION 函数名 参数定义与代码 5.1.3 函数 36 流程控制语句用于控制SQL语句、语句块、 存储过程或触发器的执行流程。 主要的流程控制语句有：见下页表。 5.1.4 流程控制语句 5.1.4 流程控制语句 37 5.1.4 流程控制语句 语句功能 BEGINEND定义语句块 BREAK退出循环 CONTINUE重新开始循环 GOTO label转移到标号label处继续执 行 IFELSE选择语 句 RETURN无条件返回语句 WAITFOR为语句执行设置延迟时间 WHILE循环语句 CASE 表达式实现多分支 5.1.4 流程控制语句 38 其他Transact-SQL语句还有： (1) /*/: 注释语句, 用于多行注释 (2) - -(注释语句):用于单行或嵌套注释 (3)DECLARE：变量声明语句。 (4)EXECUTE：存储过程执行语句。 (5) PRINT: 终端输出语句 5.1.4 流程控制语句 5.1.4 流程控制语句 39 1. BEGIN END语句 该语句将多条SQL语句封装在一起，构成一 个语句块。主要语句块就可以在IF/ELSE、 WHILE等语句中作为一个整体来执行。 语法格式： 5.1.4 流程控制语句 BEGIN 若干SQL语句 END 5.1.4 流程控制语句 40 5.1.4 流程控制语句 2. IFELSE语句 语法格式： IF 条件 语句 | 语句块1 ELSE 语句 | 语句块2 5.1.4 流程控制语句 41 5.1.4 流程控制语句 例5 查询选修0001课的学生成绩，如有大于90 分以上的，则将其姓名显示出来；若无人大 于90分，则显示“成绩优秀者为0个”。 IF EXISTS( SELECT * FROM 选课 WHERE 课号=0001 AND 成绩=90 ) SELECT 姓名 FROM 学生 JOIN 选课 ON 学生.学号=选课.学号 WHERE 课号=0001 AND 成绩=90 ELSE PRINT 成绩优秀者为0个 5.1.4 流程控制语句 42 3. WHILE语句 语法格式： 5.1.4 流程控制语句 WHILE 条件 SQL语句| 语句块 BREAK SQL语句| 语句块 CONTINUE 循环体 5.1.4 流程控制语句 43 5.1.4 流程控制语句 例6 引用已建函数get_all_avg，分别求出平 均成绩大于60、70、80、90的学生成绩信 息。 5.1.4 流程控制语句 44 5.1.4 流程控制语句 DECLARE j int SET j=60 WHILE j可以是常量、属性名、函数、子查 询和算术运算符、字符串运算符等组合的有 意义的式子。 是在多个中选择一 个表达式，它的值作为“CASE表达式”的结果值。 当所有匹配都不成立， CASE则返回NULL。 可以只有 一个WHEN子句。 是一个表达式，可以用在可以出现的任何地方。 5.1.4 流程控制语句 5.1.4 流程控制语句 50 5.1.4 流程控制语句 Case 表达式= 表达式1 表达式n 结果表达式1 结果表达式n 结果表达式n+1 END ELSE WHEN 5.1.4 流程控制语句 51 例8 将百分制分数按优、良、中、及格和不及 格五个等级记分制的形式输出。 5.1.4 流程控制语句 SELECT 学号, 课号, 成绩 = CASE 成绩/10 WHEN 6 THEN 及格 WHEN 7 THEN 中 WHEN 8 THEN 良 WHEN 9 THEN 优 WHEN 10 THEN 优 ELSE 不及格 END FROM 选课 说明： 整数除整数得 到整数（系统自 动取整） 验证： Select 10/2 5.1.4 流程控制语句 52 (2) 搜索型CASE表达式 5.1.4 流程控制语句 CASE WHEN 条件表达式1 THEN 结果表达式1 WHEN 条件表达式n THEN 结果表达式n ELSE 结果表达式n+1 END 功能：得到一个满足条件的值。如下图所示。 5.1.4 流程控制语句 53 5.1.4 流程控制语句 条件表达式n 结果表达式1 结果表达式n 结果表达式n+1 END ELSE WHEN 条件表达式1 CASE 5.1.4 流程控制语句 54 例9 将百分制分数按优、良、中、及格和不及 格五个等级记分制的形式输出。 5.1.4 流程控制语句 SELECT 学号, 课号, 成绩= CASE WHEN 成绩/10=6 THEN 及格 WHEN 成绩/10=7 THEN 中 WHEN 成绩/10=8 THEN 良 WHEN 成绩/10=9 THEN 优 WHEN 成绩/10=10 THEN 优 WHEN 成绩/10 IS NULL THEN 无成绩 ELSE 不及格 END FROM 选课 5.1.4 流程控制语句 55 7. 批处理和脚本 (1)批处理 批处理是作为一个组一起提交并执行的若干 Transact-SQL语句。 批处理可以交互地运行，或作为脚本的一部 分运行。一个脚本可以包含多个Transact- SQL批处理。 5.1.4 流程控制语句 5.1.4 流程控制语句 56 使用GO语句定义一个批处理。 使用GO语句表示一个批处理的结束。 GO 不是通用的Transact-SQL语句，是只有 SQL Server查询分析器和osql实用程序接受 的语句。 5.1.4 流程控制语句 5.1.4 流程控制语句 57 SQL Server如何处理批处理 SQL Server统一优化、编译和执行一个批处 理中的语句，但是，这些语句并不构成一个事 务，不必作为一个可恢复单元执行。 用户定义的变量作用域限制在一个批处理，所 以变量不能在GO语句后引用。 5.1.4 流程控制语句 例如 USE 教学数据库 SELECT * FROM 学生 SELECT * FROM 课程 GO 一个批处理 5.1.4 流程控制语句 58 例如： USE 教学数据库 GO CREATE VIEW 成绩_V1(学号,平均成绩 ) AS SELECT 学号, AVG(成绩) FROM 选课 GROUP BY 学号 GO SELECT * FROM 成绩_V1 GO 5.1.4 流程控制语句 5.1.4 流程控制语句 59 批处理规则 CREATE PROCEDURE、CREATE VIEW 、CREATE TRIGGER、CREATE RULE 、CREATE DEFAULT这些语句不能在一个 批处理中联合使用，并且也不能与其他语句 联合使用。即在一个批处理中，只能有一个 CREATE语句，其他语句都只能视为该语句 的的一部分。 5.1.4 流程控制语句 5.1.4 流程控制语句 60 不能在一个批中修改表结构，然后在同一个 批中引用刚修改的列。 如果EXECUTE语句是批处理的第1句，则 可以省略，否则执行任何不是批处理中第1 句的存储过程都必须包含EXECUTE关键字 。 一个批处理中用到的变量必须在其中定义， 不能引用其他批处理中定义的变量。 5.1.4 流程控制语句 5.1.4 流程控制语句 61 (2) 脚本 脚本是作为一个文件保存的一个或多个 Transact-SQL语句。 脚步可以在SQL Server分析器或任何文本编 辑器中编写和保存，以.sql为扩展名。 脚本必须在SQL Server分析器中执行。 脚本主要用于：重新创建数据库或数据对象 ，或重复执行一些语句。 5.1.4 流程控制语句 5.1.4 流程控制语句 62 5.2.1 存储过程的概念 1.存储过程的定义 存储过程是存放在服务器上的预先编译好的一组完 成特定功能的Transact-SQL语句集。 5.2 存储过程 5.2.1 存储过程的概念 63 5.2.1 存储过程的概念 2.存储过程的特点 使用存储过程可以减少网络数据流量。 用户不是在网络上发送几百条SQL语句，而是通过 发送一条调用过程语句来执行一个复杂的操作，这样 减少了服务器和客户机之间传递的请求数。 5.2.1 存储过程的概念 增强代码的重用性和共享性。 存储过程可以被多个应用程序多次调用，实行代码 的重用性和共享性。 64 5.2.1 存储过程的概念 使用存储过程可以加快系统的运行速度。 存储过程第一次执行后，执行计划就被存放在高 速缓存中。以后执行时，不必再执行优化处理，从 而加快了系统运行速度。 5.2.1 存储过程的概念 存储过程提高了数据的安全性。 系统管理员可以只授权用户访问存储过程的权限 ，而不授予用户访问存储过程中涉及的到的表的权 限。从而有效地保护了数据表中存储过程涉及不到 的数据的安全性。 65 5.2.1 存储过程的概念 3.存储过程的分类 系统存储过程 系统存储过程(由前缀sp_标识)存放在 master数据库中。可以作为命令直接执行。 其中大部分存储过程都可以在任何用户数据库 中执行。 用户存储过程：用户自定义的存储过程。 5.2.1 存储过程的概念 66 5.2.1 存储过程的概念 扩展存储过程 扩展存储过程(主要由前缀xp标识)是指在 SQL Server环境外部执行的DLL。但是，它 们可以被加载到SQL Server系统中，并按照 存储过程方式执行。 远程存储过程 远程存储过程是指从远程服务器上调用的存储 过程。 5.2.1 存储过程的概念 67 5.2.2 存储过程创建和执行 1. 创建存储过程语句 CREATE PROCEDURE 存储过程名 ;num 参数1 数据类型 =默认值 OUTPUT , 其他参数 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS BEGIN SQL语句系列 END 5.2.2 存储过程创建和执行 68 5.2.2 存储过程创建和执行 说明 ;num：可选的整数。用来对同名若干过程编 号，如 proc_s;1, proc_s;2, proc_s;3等。 采用相同名字加编号区别若干过程的优点是删 除时方便，如，DROP PROCEDURE proc_s一条命 令把3个过程都删了。 5.2.2 存储过程创建和执行 69 5.2.2 存储过程创建和执行 default：参数的默认值。如果定义了默认 值，不必指定该参数的值就可调用。默认值必 须是常量或NULL。如果过程将对该参数使用 LIKE关键字，那么默认值中可以包含通配符 (%、_、)。 OUTPUT：表示该参数是返回参数。参数可 将信息返回给调用过程。Text、ntext、 Image参数可用作OUTPUT参数。 5.2.2 存储过程创建和执行 70 5.2.2 存储过程创建和执行 RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION RECOMPILE：表明该过程在运行时重新编译。 ENCRYPTION ：表示系统加密syscomments表中 包含CREATE PROCEDURE语句文本的条目。 加密后即使是过程创建者 本人也无法查看过 程定义文本。 5.2.2 存储过程创建和执行 71 5.2.2 存储过程创建和执行 例如： USE 教学数据库 GO CREATE PROC c_s AS SELECT * FROM 学生 WHERE 所属系 LIKE 计算机% GO 5.2.2 存储过程创建和执行 72 5.2.2 存储过程创建和执行 2.执行存储过程语句 EXECUTE return_status= 存储过程名;number |存储过程名变量 参数1=值1, 参数n=值n | 变量 OUTPUT|DEFAULT WITH RECOMPILE 5.2.2 存储过程创建和执行 73 5.2.2 存储过程创建和执行 参数说明: return_status, 为一整型变量, 用于保存 存储过程的返回状态值。 存储过程名变量：表示存储过程名称的变量。 5.2.2 存储过程创建和执行 变量 output default:用来保存过程中的 OUTPUT参数的返回值。DEFAULT提供参 数的默认值 74 5.2.2 存储过程创建和执行 3. 创建无参数的存储过程 例10 创建一个存储过程。完成显示所有学生 的平均成绩。 CREATE PROC SC_proc;1 AS SELECT 学号, AVG(成绩) as 平均成绩 FROM 选课 GROUP BY 学号 5.2.2 存储过程创建和执行 75 5.2.2 存储过程创建和执行 4. 创建带有输入参数的存储过程 例11 创建一个存储过程。显示指定学号的学 生的平均成绩。学号作为参数. CREATE PROC SC_proc;2 sno char(6) AS SELECT 学号, AVG(成绩) as 平均成绩 FROM 选课 WHERE 学号 LIKE sno GROUP BY 学号 5.2.2 存储过程创建和执行 76 5.2.2 存储过程创建和执行 调用存储过程sc_proc;2 方法1:declare temp1 char(6) set temp1=030101 exec 教学数据库.dbo.sc_proc;2 temp1 方法2: exec sc_proc;2 030101 5.2.2 存储过程创建和执行 77 5.2.2 存储过程创建和执行 5. 创建带有输出参数的存储过程 例12 创建存储过程。完成求指定学号的学生的 平均成绩，并将该成绩保存在变量avg中。 CREATE PROC SC_proc;3 sno char(6), avg int OUTPUT AS SELECT avg= AVG(成绩) FROM 选课 WHERE 学号= sno GROUP BY 学号 5.2.2 存储过程创建和执行 78 5.2.2 存储过程创建和执行 执行存储过程SC_proc;3 : DECLARE temp CHAR(6), avg_out INT SET temp=030101 EXEC SC_proc;3 temp,avg_out OUTPUT PRINT 030101的平均成绩为:+ CAST(avg_out AS CHAR(3) 过程中输出 参数_avg 外部变量 avg_out 5.2.2 存储过程创建和执行 79 5.2.2 存储过程创建和执行 6.返回存储过程的执行状态 每个存储过程的执行,都将自动返回一个整数状 态值,用于告诉用户调用存储过程的执行情况。 调用程序可以根据返回值作相应的处理。 用户可以用大于0或-1-99的来定义自己的返 回状态值，以表示不同的执行结果。 一般而言，0表示存储过程执行成功，-1-99 之间的数表示过程执行失败。 5.2.2 存储过程创建和执行 80 5.2.2 存储过程创建和执行 例13 检查给定学号的学生有无不及格的记录， 有则返回5,无则返回0并输出该学生的 学习记录。如果没有提供参数学号，则返回15. CREATE PROC SC_PROC;4 sno CHAR(6)=NULL AS IF sno IS NULL RETURN 15 /*未给出该生学号*/ 创建返回执行状态的存储过程 5.2.2 存储过程创建和执行 81 5.2.2 存储过程创建和执行 ELSE IF EXISTS(SELECT * FROM 选课 WHERE 学号= sno AND 成绩 可视化操作：选定存储过程，在其上打开快 捷菜单，选择“编辑” 菜单命令，打开编辑窗 口。修改完成后，再重新执行一次即可。 或 5.2.4查看、修改和删除存储过程 92 * 5.2.5 系统存储过程 常用的系统存储过程如下表 存储过程功能 Sp_help 对象名 提供指定数据库对象的信息 Sp_helptext 对象名查看指定数据库对象(视图 存储过程触发器等)的文本信 息 Sp_helpdb 数据库名查看数据库信息 Sp_recompile 表名在存储过程(与表相关的)下次 执行时，重新编译。 5.2.5 系统存储过程 93 *5.2.5 系统存储过程 存储过程功能 Sp_depends 对象名查看指定数据库对象(视图 存储过程触发器等)所引用的 对象信息 sp_bindrule 规则名, 对象名 将规则绑 定到指定对象 sp_unbindrule 对象名 将规则从指定对象上松绑 5.2.5 系统存储过程 94 *5.2.5 系统存储过程 例：查看与选课表相关的所有对象。 sp_depends 选课 表值函数 标量值函数 5.2.5 系统存储过程 95 5.3 触发器 SQL Server提供了两种主要机制来强制实施数 据库的完整性：约束和触发器。 触发器可以实现比check约束更为复杂的完整 性约束。 5.3 触发器 5.3 触发器 96 触发器：是一种特殊的存储过程，在用户试图 更新触发器保护的数据时自动执行。触发器: (1) 总是与一个表相连。 触发器是在单个表或视图上定义，这个表称为 触发器表。每个表上可以建若干触发器。 (2) 是自动激活的。 当对一个表中数据执行插入、删除、修改操作 时，如果对该表上这个操作定义了触发器，则 该触发器自动执行，这是不可阻挡的。 5.3.1 什么是触发器 5.3 触发器 97 (3). 不能直接调用 与标准的存储过程不同，触发器不能直接调用 ，也不能传递或接受参数。 (4). 是一个事务 触发器和激活它的语句作为一个事务处理，可 以从触发器中的任何位置撤销。触发器可以包 括ROLLBACK TRANSACTION语句，激活触 发器的语句可以看成隐含事务的开始。 5.3.1 什么是触发器 5.3 触发器 98 Deleted和inserted 表: 当用户对触发器表执行更新操作时，SQL Server自行为每个触发器创建和管理这两个表 。它们是逻辑表，存放在内存中，用户不能直 接对这两个表进行修改。这两个表的结构与触 发器表的结构相同。触发工作完成后，与触发 器相关的这两个表将被删除。 5.3.1 什么是触发器 5.3 触发器 99 1. inserted 表 inserted表用于存储INSERT和UPDATE语句 所影响的行的副本。在一个插入或更新事务处 理中，新建行被同时添加到inserted表和触发 器表中。 inserted表中的行是触发器表中新行 的副本。 5.3.1 什么是触发器 5.3 触发器 100 2. deleted 表 deleted表用于存储DELETE和UPDATE语句 所影响的行的副本。在执行DELETE或 UPDATE语句时，行从触发器表中删除，并传 送到deleted 表， deleted 表和触发器表一般 没有相同的行。 5.3.1 什么是触发器 5.3 触发器 101 5.3.2 创建触发器 1. 语法格式 CREATE TRIGGER 触发器名 ON 表 -给定建立触发器的表 WITH ENCRYPTION -加密存储触发器定义 FOR|AFTER|INSTEAD OF 触发时间 INSERT,DELETE,UPDATE -触发事件 AS 触发器要完成的操作 -触发后的动作 5.3.1 创建触发器 102 5.3.2 创建触发器 说明： AFTEER：在触发操作执行完后触发。 FOR关键字：与AFTER相同。 INSERTED OF ：代替触发动作(触发语句) 进行操作，并在处理约束之前触发。 WITH ENCRYPTION：加密存储触发器存 放在syscomments中的文本。 5.3.1 创建触发器 103 5.3.2 创建触发器 注意： 创建触发器的权限默认为表的所有者，且不 能将该权限转让给其它用户。 不能在临时表上创建触发器，但在触发器中 可引用临时表。 触发器允许嵌套。最大嵌套数32。 5.3.1 创建触发器 104 触发器中不允许有下列语句： ALTER DATABASE, CREATE DATABASE, DISK INIT, DISK RESIZE, DROP DATABASE, LOAD DATABASE等等。 5.3.2 创建触发器 5.3.1 创建触发器 105 5.3.2 创建触发器 2. INSERT触发器 插入触发器的执行过程： 首先执行INSERT语句的插入操作。将新行 插入到触发器表和inserted表中。 然后执行触发器中的语句。如果执行到 ROLLBACK语句，则系统将回滚整个操作。 5.3.1 创建触发器 106 5.3.2 创建触发器 例14 建立一个INSERT触发器。每当在“选 课”表中插入一条记录时，检查学号在学生表 中是否存在，若不存在，拒绝插入，否则允 许。 5.3 触发器 107 5.3.2 创建触发器 CREATE TRIGGER sc_insert ON 选课 FOR INSERT AS BEGIN DECLARE sno CHAR(6) Select sno=学号 FROM inserted IF NOT EXISTS(SELECT * FROM 学生 WHERE 学号=sno) BEGIN PRINT 该生不存在！ ROLLBACK TRAN END END 5.3 触发器 108 5.3.2 创建触发器 测试： INSERT INTO 选课(学号,课号,成绩) VALUES(000001,011,88) 5.3 触发器 109 5.3.2 创建触发器 create trigger s_sno_numeric on dbo.学生 for insert, update as begin declare sno char(6) select sno=inserted.学号 from inserted if isnumeric(sno)=0 begin print 学号必须是数字构成，撤销此插入 delete from 学生 where 学号 like sno end end 例15: 建立一个触发器,学号必须是数字字符. 5.3 触发器 110 5.3.2 创建触发器 create trigger sc_count on 选课 for insert as begin declare cno char(6) select cno=课号 from inserted if 160(select count(*) from 选课 where 课号=cno) begin Print 选课人数已满 rollback transaction end end 例16: 建立一个触发器,一门功课的选课人数不能大于 160. 5.3 触发器 111 5.3.2 创建触发器 3. delete触发器 执行过程: 首先执行DELETE语句的删除操作。将要删除 的记录存放到deleted表中,然后删除触发器表 中相应的行。 执行触发器中语句。如果执行到ROLLBACK 语句，则系统将回滚整个操作。 5.3 触发器 112 5.3.2 创建触发器 例17 建立一个DELETE触发器，每当在学生表 中删除一条记录时，也将在选课表中删除相应 的记录。 CREATE TRIGGER S_delete ON 学生 FOR DELETE AS BEGIN DECLARE sno CHAR(6) Select sno=学号 FROM deleted 5.3 触发器 113 5.3.2 创建触发器 IF EXISTS(SELECT * FROM 选课 WHERE 学号=sno) BEGIN DELETE FROM 选课 WHERE 学号=sno PRINT 成功删除! END END 测试：(在学生表加111111学生,练习) DELETE FROM 学生 WHERE 学号=111111 5.3 触发器 114 5.3.2 创建触发器 4. UPDATE触发器 执行过程： 首先执行UPDATE语句。将修改前的旧行插 入到deleted表中,再修改触发器表中该行信息, 将修改后的新行插入到inserted表。 执行触发器中的语句。如果执行到 ROLLBACK语句，则系统将回滚整个操作。 5.3 触发器 115 5.3.2 创建触发器 例18 建立一个UPDATE触发器，每当在选课表 中修改一条记录时，也将在c_avg表中更新相 应的记录。 Create table c_avg (课号 char(4) , 平均成绩 int ) Insert Into c_avg Select 课号, Avg(成绩) As 平均成绩 From 选课 Group By 课号 c_avg(课号, 平均成绩) 5.3 触发器 116 5.3.2 创建触发器 CREATE TRIGGER sc_update ON 选课 FOR UPDATE, INSERT AS DECLARE cno CHAR(4) Select cno=inserted.课号 FROM inserted 5.3 触发器 117 5.3.2 创建触发器 IF NOT EXISTS(SELECT * FROM 选课 WHERE 课号 like cno) BEGIN INSERT INTO C_avg SELECT 课号, AVG(成绩) FROM 选课 WHERE 课号= cno GROUP BY 课号 PRINT 新增加了一条课程记录！ END ELSE 5.3 触发器 118 5.3.2 创建触发器 BEGIN UPDATE C_avg SET 平均成绩=(SELECT AVG(成绩) FROM 选课 WHERE 课号= cno GROUP BY 课号) WHERE 课号= cno PRINT 成功更新课程的平均成绩！ END 5.3 触发器 119 5.3.2 创建触发器 测试1：(在学生表加111111学生,在选课表中增加 若干课的成绩练习) UPDATE 选课 SET 成绩=80 WHERE 学号= 111111 AND 课号= 0001 测试2： UPDATE 选课 SET 成绩=90 WHERE 学号=