第3章 补充内容Transact-SQL语言

1、Transact-SQL语言 1. 1. T-SQLT-SQL语言简介语言简介 2. 2. T-SQLT-SQL编程编程 3. 3. T-SQLT-SQL提供的函数提供的函数 4. 4. T-SQLT-SQL游标游标 5. 5. T-SQLT-SQL存储过程存储过程 6.6. 触发器触发器 7.7. 函数函数 T-SQL，即事务SQL（Transact-SQL），是MS SQL Server和Sybase对标准SQL的扩展版本，它不仅与ANSI SQL 标准兼容，还在存储过程等许多方面作了扩充和增强。本节本节 主要介绍如下几个问题：主要介绍如下几个问题： 一一.T-SQL.T-SQL语言简介语

2、言简介 u T-SQL的存储过程：由SQL语句组成，分系统存储过程和用 户自定义存储过程。 由三部分组成，即：SQL语句、函数和存储过程。 u T-SQL的子语言：即DDL 、DML和DCL三个子语言。 u T-SQL的函数：系统函数、聚集函数、数学函数、字符串函 数、数据类型转换函数和日期函数等。 1. T-SQL1. T-SQL组成组成 2. T-SQL2. T-SQL的数据类型的数据类型 SQL Server有两类数据类型：系统数据类型系统数据类型和用户自定义类型用户自定义类型。 T-SQLT-SQL系统数据类型系统数据类型(1/2)(1/2) 类型数值范围 bigint从 -263 到

3、 263-1 的整型数据（所有数字）。 int从 -231 到 231-1 的整型数据（所有数字）。 smallint从 -215 到 215-1 的整型数据（所有数字）。 tinyint从 0 到 255 的整数数据。 bit1 或 0 的整数数据。 decimal(numeric)从 -1038 +1 到 1038 1 的固定精度和小数位的数字数据。 money 货币数据值介于 -263 与 263 - 1 之间，精确到货币单位 的千分之十。 datetime 从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精 确到百分之三秒。 smalldateti

4、me 从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。 T-SQLT-SQL系统数据类型系统数据类型(2/2)(2/2) 类型数值范围 float从 -1.79E + 308 到 1.79E + 308 的浮点精度数字。 real从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。 char固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符。 varchar可变长度的非 Unicode 数据，最长为 8,000 个字符。 text可变长度的非 Unicode 数据，最大长度为 231 - 1 个字符。 ncha

5、r固定长度的 Unicode 数据，最大长度为 4,000 个字符。 nvarchar可变长度 Unicode 数据，其最大长度为 4,000 字符。 ntext可变长度 Unicode 数据，其最大长度为 230 - 1 个字符。 binary固定长度的二进制数据，其最大长度为 8,000 个字节。 varbinary可变长度的二进制数据，其最大长度为 8,000 个字节。 image可变长度的二进制数据，其最大长度为 231 - 1 个字节。 用户自定义类型 利用SQL Server的系统数据类型，可定义用户自定义类型。 u 具体方法 利用系统存储过程sp_addtype、sp_dropt

6、ype和sp_help， 分别创建、删除和查看用户定义类型。 u sp_addtype 命令语法：sp_addtype 类型名, 系统数据类型名, 属 性 关于数据的说明：属性有3种选择。 。NULL：允许列为空值 。NOT NULL：不允许列为空值 。IDENTITY：指定列为标识列，每张表只能有一个标 识列。IDENTITY ( seed , increment ) 用户自定义类型（续）用户自定义类型（续） u sp_addtype EXEC sp_addtype notes, text, NULL EXEC sp_addtype test, char(2), NOT NULL u sp_

7、droptype 命令语法：sp_addtype 类型名 u sp_help 命令语法：sp_help 类型名 (1) 概念 由多条T-SQL语句组成，类似于DOS中的批命令，通 过一个“批”将多条SQL语句用一个GO命令提交给服务器， 由服务器按一个“事务”来执行该批。 (2)两种类型 u 交互批：在交互使用SQL命令的环境中，用GO作为一个 批的结束，并提交系统执行。交互方式下，一次只能提交一 个批。 u 文件批：将多个批放在一个文件中，提交给系统执行， 其中的每个批均以GO结束。大多数的SQL语句可放在一个 批中。 SQL ServerSQL Server中的批中的批 用“变量名”表示，

8、一般由用户定义和使用。定义语法如 下： 2.2.注释注释(Comment)(Comment)方式方式 (1) 多行时，用 /* . */ (2) 单行时，用 - 3.3.变量变量 DECLARE 变量名 类型 , n (1) 局部变量 u 说明 (1) 变量一旦定义，系统自动赋NULL值。 (2) 对局部变量的赋值用SELECT语句。 u 示例：4-19 DECLARE myqty int, msg varchar(40) (1) 局部变量（续） u 两种赋值方式 直接赋值方式： SELECT/SET 变量名=值 例如：select myqty=60 间接赋值方式： 从表中取值赋给变量，但应保

9、证类型一 致。若从表中返回的是多个值，则取最后一个值赋给变量。 例如： SELECT s=price FROM titles WHERE title_id=bu395 u 查看变量值 SELECT 变量名 表示及概念： 变量名 ，由系统定义，用户可使用 之。 作用：用于指明系统运行过程中的运行状态，用户只 能引用，不能修改和定义。 (2) 全局变量 常用的全局变量： error：返回最后一个语句产生的错误码。 rowcount：返回语句执行后受影响的行数。 version：SQL SERVER版本号。 trancount：事务计数。 FETCH_STATUS：返回被 FETCH 语句执行的最

10、后游标的状态。 4.4.流程控制语句流程控制语句 (1) IF (条件关系式) ELSE IF 注意：如果关系式含有SELECT语句，则该SELECT语句须用圆括号 括起，其嵌套级别最多为150。 u 用途及用法 条件分支，同一般高级语言中的条件分支语句用法。 u 举例： IF (SELECT AVG(price) FROM titles)=20 UPDATE titles SET price=price*1.3 ELSE PRINT 平均价格未知! (2) BEGIN . END u 用途及用法 用于界定由多条SQL语句组成的语句块。类似于C/C+中的 “ ”或PASCAL语言中的“BEGI

11、NEND”的用法。 u 举例： DECLARE avg_price money SELECT avg_price=AVG(price) FROM titles IF avg_price20 BEGIN UPDATE titles SET price=price*1.5 PRINT 价格提高50% END ELSE BEGIN UPDATE titles SET price=price*1.3 PRINT 价格提高50% END (3) WHILE .BREAK/CONTINUE 4.4.流程控制语句流程控制语句 u 用途及用法 u 举例： 循环控制语句。同C/C+中的“WHILE BREAK/

12、CONTINUE”。 WHILE (SELECT AVG(price) FROM titles) 30 BREAK END SELECT title_id, price FROM titles 4.4.流程控制语句流程控制语句 (4) GOTO .：跳到用户定义的标号处；建议尽量少用。 (5) RETURN：无条件退出；同C/C+中RETURN的用法。 (6) IF NOT EXISTS：判断是否有数据存在 。语法如下： IF NOT EXISTS (SELECT 语句) 语句块 示例：IF NOT EXISTS使用示例。 IF NOT EXISTS （SELECT * FROM titles

13、 WHERE title_id=010101） BEGIN PRINT 没有010101的任何记录 RETURN END 5.5.信息显示信息显示 (7) WAITFOR：延迟某段时间 示例：WAITFOR DELAY 00:30:00 /* 延迟30分钟 */ 语法：WAITFOR DELAY 日期时间格式的时间值 | TIME 时间 | (1) Print . (2) Print msg 作用：显示局部变量的值 DECLARE PrintMessageDECLARE PrintMessage NVARCHAR(50); NVARCHAR(50); SET PrintMessage = NT

14、hisSET PrintMessage = NThis message was printed on message was printed on + + RTRIM(CAST(GETDATE() AS NVARCHAR(30) RTRIM(CAST(GETDATE() AS NVARCHAR(30) + N.; + N.; PRINT PrintMessagePRINT PrintMessage; ; 4.4.流程控制语句流程控制语句 6.6.RAISERROR 用途：系统有很多系统信息及其代码（代码值在20000以下）， 用户也可用RAISERROR，自己定义错误信息及其代码（其 代码值应

15、在20000以上）。 语法：RAISERROR 错误代码 错误信息 | 局部变量 , 参数表 举例：RAISEERROR使用示例。 DECLARE table_name varchar(30) SELECT table_name=TITLE RAISERROR 99999 表%1！不存在。，table_name 说明： 错误代码将放入全局变量error中，且须大于20000 局部变量的类型须为char或varchar，错误信息长度小于255 三.T-SQL提供的函数 1.1.字符串操作字符串操作 (1) 连接操作 “+” T-SQL提供的函数包括：字符串函数、日期函数、数据类型 转换函数、数学

16、函数、聚集函数和系统函数等。这些函数在DML 的各语句中被广泛使用。 u 用途：使用“+” 连接字符串。结果字符串最大不超过 255 个字符。 u 示例：China+，+Chengdu=China, Chengdu (2) 字符串函数 Substring、right、upper、char、space 2.2.类型转换类型转换 注：注：“”表示本身类型的转换；表示本身类型的转换；“i”i”表示隐式转换；表示隐式转换；“e”e”表示表示 显式转换显式转换 realfloatcharvarcharmoney realieeI floatieeI chareeiie varchareeiie mone

17、yiiii SQL Server中的类型转换分两种，即：显式（Explict）和隐式 （Implict）。显式用convert函数转换，隐式由系统自动进行。下表 是SQL Server的类型转换。 (2) 显式转换 作用：将表达式按指定风格转换成指定的类型，其中的style 只用于日期类型到字符串的转换，用于指定日期时间 的输出格式。其他类型，则省略style参数。 语法：convert(类型符，表达式，style) (1) 隐式转换 2.2.类型转换类型转换 显式转换需使用convert函数。 stylestyle取值输出格式 2yy.mm.dd 3dd/mm/yy 4dd.mm.yy 5d

18、d-mm-yy 102yyyy.mm.dd 103dd/mm/yyyy 104dd.mm.yyyy 105dd-mm-yyyy 示例： SELECT convert(char(10),pubdate,105) FROM titles WHERE title_id=PC1035 其中，pubdate为日期型，返回值为字符型30-06-1986。 3.3.有关日期的几个函数有关日期的几个函数 (1) getdate() (2) datename()与datepart() datename(日期元素,日期表达式)：以字符串形式返回日期元素 指定的日期的名字。 用途：将服务器OS的时间送客户机。 示例

19、：SELECT getdate() datepart(日期元素,日期表达式)：以数字形式返回日期元素指定 的日期的名字。 日期元素是什么呢？ 日期元素及其指定返回的日期部分为：日期元素及其指定返回的日期部分为： 示例：示例： Select datename(mm,pubdate) from titiles where title_id=Bu1032 返回June，如用datepart则返回 6。 yy：返回日期表达式中的年year或年数。 qq：返回日期表达式表示的季quarter或季数。 mm：返回日期表达式中的月month或月数。 dw：返回日期表达式表示的星期几day of week。

20、dy：返回日期表达式表示的一年中的第几天day of year dd：返回日期表达式中的天或天数day。 wk：返回日期表达式表示的一年中的第几个星期或星期数。 hh：返回日期表达式中的小时或小时数。 3.3.有关日期的几个函数有关日期的几个函数 4.4.数学函数数学函数 (3) dateadd()与datediff() dateadd(日期元素，数值，日期表达式)：将数值转换成日期元素指定的部分 加到日期表达式上返回。 datediff(日期元素，较早日期表达式，较晚日期表达式)：两个日期相减后，按 日期元素指定部分转化后返回。 函函 数数示示 例例结结 果果 abs(数值表达式)abs(-

21、100)100 ceiling(数值表达式)ceiling(99.2)100 floor(数值表达式)floor(99.2)99 round(数值表达式，整数表达式)round(66.2387,2)66.24 exp(浮点表达式)exp(0)1 rand(整数)rand(23) log(浮点表达式)log(1)0 pi()pi()3.14159265. power(数值表达式，指数表达式)power(2,10)1024 sqrt(数值表达式)sqrt(4)2 sin(浮点表达式),cos(浮点表达式),tan(浮点表达式)sin(pi()0 5.5.聚集函数聚集函数 作用：返回统计值，该类函数

22、只能放在SELECT中，不能出现在 WHERE子句中。 6.isnull6.isnull函数函数 语法：isnull(列名，值) 功能：当列值为空（NULL）时，用指定的数值代替之。 用途：在聚集函数中，一般均会将空值的列排除在外。若想将 空值的列包含进来参加运算，即可用此函数。该函数不 会替代表中的NULL值。 举例：例SELECT AVG( isnull(price, 0) ) FROM titles 1.1.游标游标(CURSOR)(CURSOR)及其作用及其作用 SQL Server在内存开辟一段缓冲区，SELECT查询的结果集合 按行放入该缓冲区，并且提供了存取该缓冲区数据的“行指针

23、”。 用户利用该指针即可存取和处理各行数据。为高级编程语言提供 了按行处理查询结果集合的途径。 2.2.游标生命周期游标生命周期 包括：定义、打开游标（Open Cursor）、存取 （Fetch）数据、 关闭游标(Close cursor)和释放（Deallocate）游标缓冲区。 类似于程序设计语言中的“文件”。对应关系为： 游标定义文件的创建 打开游标打开文件 存取游标存取文件 关闭游标关闭文件 释放游标删除文件 游标指针文件指针 Declare 游标名 CURSOR FOR Select语句 FOR READ ONLY | UPDATE OF 列名表 3.3.定义游标定义游标 u 语法

24、 两类游标：只读（READ ONLY）游标和更新(UPDATE)游标。 u 说明 READ ONLY：不能通过游标对表进行修改。 UPDATE：可以通过游标对表进行修改。 游标的定义须作为单独的事务批提交。 4.4.打开游标打开游标 ： OPEN 游标名 用法：Fetch一次，指针下移一行。一次只能存取一行。注意： 作用：将当前行各字段的内容存入局部变量列表中，并且指针 下移一行。 使用前应事先定义好局部变量。 影响两个全局变量，即： 5.FETCH5.FETCH u 语法 u 语法 作用：执行查询并将查询结果集放入内存缓冲区，游标指针指向结 果第一行。 ： FETCH 游标名 INTO 局部

25、变量列表 fetchstate：0成功；1失败 2没有数据。 rowcount ：每移一次累加一。 6.Close6.Close和和DeallocateDeallocate ： CLOSE 游标名u CLOSE语法 用法：游标关闭后，不能再使用该游标中的数据，须再次打开，才 能存取数据。 ： Deallocate Cursor 游标名u Deallocate语法 用法：游标释放后，不能再打开，须重新定义并打开后才能使用。 7.7.用游标对数据操作用游标对数据操作 u 删除操作：Delete 表名 Where Current of 游标名 作用：删除表中与游标位置对应的行，要求表有惟一索引。 说

26、明：删除后指针不动，下面的行自动上移。 u 修改操作：Update 表名 Set 列名=值 , Where Current of 游标 名 作用：修改表中与游标位置对应的行，要求表有惟一索引。 u功能要求：须是可更新游标。可实现对表中数据的删除与修改。 8.8.游标使用示例游标使用示例 -用流控制语言统计tblSelCourse表中的成绩为A、B、C、D各个层次的学生数。假如 规定：优-90，100 良-80，89 中-60，80 及格-0，60 declare a int,b int,c int,d int Select a=0 ,b=0,c=0,d=0 declare score deci

27、mal(4,2) DECLARE SC_csr CURSOR FOR SELECT score FROM SC OPEN SC_csr FETCH SC_csr into score WHILE FETCH_STATUS = 0 BEGIN if score=60 and score=80 and score=90 set a=a+1 FETCH SC_csr into score END select a 优, b 良, c 中, d 及格 CLOSE SC_csr DEALLOCATE SC_csr 1.1.存储过程概念存储过程概念 存储过程是存储于RDBMS中的一段由SQL语句组成的程序

28、。在SQL Server中，它是一种数据库对象，建立时由RDBMS编译和优化，其执行代 码存储于数据库中。用户可以通过调用存储过程来操纵数据库。 类型：分系统存储过程与用户自定义存储过程两类。 系统存储过程：由SQL Server系统提供，用户或数据库管理员可使用 它管理和维护数据库中的各种数据信息和对象。系 统存储过程以“SP_”开头。 用户自定义存储过程：由用户自己定义，本节主要介绍这种。 好处：常用于实现经常使用的数据操作，除执行速度快外，具有： 实现较复杂的完整性约束，如动态完整性约束； 可在程序中被反复使用，有助于程序的模块化； 有助于提供安全性； 减少网络流量； 可在命令行或批中调

29、用。在批中调用，若非第一条语句，应加 exec 1.1.创建存贮过程创建存贮过程 Create Proc 过程名 As SQL语句 Return 2.2.执行执行 3.3.查看查看 sp_helptext 过程名 4.4.改名改名 sp_rename 旧过程名，新过程名 注意：过程名不能与其他 数据库对象重名。 5.5.删除删除 Drop Proc 过程名 (1) 创建 6.6.带传递参数的存贮过程带传递参数的存贮过程 Create Procedure 过程名 ( 输入参数变量 类型 =DEFAULT , . 输出参数变量 类型 OUTPUT ) WITH RECOMPILE As SQL 语

30、句 Return 存储过程执行状态值 说明： 输出参数需要用关键字OUTPUT标明； WITH RECOMPILE规定每次执行是否需要重编译； return返回存储过程执行的状态值，但不同于输出参数。 (2) 传递参数和调用执行 exec 状态接收变量=过程名 输入参数1 ，输入参数2， 接收输出的局部变量 OUTPUT 6.6.带传递参数的存贮过程带传递参数的存贮过程 说明： 调用前，应定义一个接收输出的局部变量； 调用时，参数的传递按存储过程中定义的顺序进 行，但可使用“变量=参数值”的格式打乱。 在接收输出的变量后加关键字OUTPUT。 “状态接收变量”用于接收存储过程执行的状态， 一般

31、应先定义为整型。 8.8.对存贮过程的限制对存贮过程的限制 (1)某些命令不能放在过程中 Create View/Default/Rule/Trigger/Proc等。 (2)不能在同一过程中刚删除一个DB对象，又创建同名的对象。 (3)嵌套级数=16。 USE AdventureWorks; GO IF OBJECT_ID ( HumanResources.usp_GetEmployees, P ) IS NOT NULL DROP PROCEDURE HumanResources.usp_GetEmployees; GO CREATE PROCEDURE HumanResources.us

32、p_GetEmployees lastname varchar(40), firstname varchar(20) AS SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = firstname AND LastName = lastname; GO (1) 存储过程创建 8.8.存贮过程示例存贮过程示例 存储过程调用存储过程调用 EXECUTE HumanResources.usp_GetEmployees Ackerman, Pi

33、lar; n- Or nEXEC HumanResources.usp_GetEmployees lastname = Ackerman, firstname = Pilar; nGO n- Or nEXECUTE HumanResources.usp_GetEmployees firstname = Pilar, lastname = Ackerman; nGO n- Or, if this procedure is the first statement within a batch: nHumanResources.usp_GetEmployees Ackerman, Pilar; 触发

34、器的基本概念和优点触发器的基本概念和优点 n概念: n触发器是一种特殊的过程，不带参数，不被用户和程序调用， 只能由用户对DB中表的操作（即：插入、删除和修改3种操作） 触发。也就是说，它是由操作激发的过程。因此，可利用触 发器来维护表间的数据一致性。 n特征： n与表紧密联系，离开了表它将不复存在（这点与约束十分类 似）。 n触发器只能在表上建立，且一张表最多建立3个触发器 n操作时被触发而执行，不能由用户直接调用。 触发器执行过程中的两个表（触发器执行过程中的两个表（1/2） n触发器是在表上建立的，我们将触发器所依附 的表称为触发器表。当修改表的数据而引发了 触发器时，触发器将执行一系列

35、T-SQL命令， 在执行这些动作之前系统首先自动建立两个表， Deleted表与Inserted表。这两个表临时驻留在 内存当中，其结构与触发器表完全相同，一旦 触发器执行完毕，两个表也随之消失。 n可以使用Deleted表与Inserted表测试某些数据 修改的效果以及设置触发器操作的条件，但不 能直接修改这两个表中的数据。 触发器执行过程中的两个表（触发器执行过程中的两个表（2/2） nInserted表用于存放执行INSERT或UPDATE操作时向触 发器表（被插入数据的表）中插入的数据行，即新的 数据行被同时插入到两个表触发器表和Inserted 表中 。 nDeleted表存放由于执

36、行DELETE或UPDATE操作时从触发 器表中删除的数据行。 nUPDATE操作相当于先执行DELETE操作，然后再执行 INSERT操作，因此要用到Deleted与Inserted两个表。 使用触发器的优点使用触发器的优点 n强制比强制比check约束更复杂的数据完整性约束更复杂的数据完整性 n使用自定义的数据错误使用自定义的数据错误 n实现数据库中多张表的级联修改实现数据库中多张表的级联修改 n比较数据库修改前后的数据状态比较数据库修改前后的数据状态 n维护非规范化数据维护非规范化数据 触发器类型触发器类型 nINSTEAD OF触发器：触发器：在触发后将执行一组新在触发后将执行一组新

37、的的T-SQL语句用以代替引起触发器执行的语句语句用以代替引起触发器执行的语句 （原有语句没有执行，仅执行新语句）。该类（原有语句没有执行，仅执行新语句）。该类 触发器既可以在表上定义，也可以在视图上定触发器既可以在表上定义，也可以在视图上定 义。义。 nAFTER触发器：触发器：在引起触发器执行的语句执行在引起触发器执行的语句执行 完毕后，再执行触发器中的语句。这类完毕后，再执行触发器中的语句。这类只能在只能在 表上定义。表上定义。 触发器的创建触发器的创建 n使用使用Mangement Studio创建创建 n使用T-SQL语句创建触发器 CREATE TRIGGER trigger_na

38、me ON table | view FOR | AFTER | INSTEAD OF INSERT ， DELETE ， UPDATE WITH ENCRYPTION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n sql_statement .n CREATE TRIGGER参数说明（参数说明（1/3） 1.trigger_name：是触发器的名称，必须符是触发器的名称，必须符 合标识符规则，并且必须在数据库中惟一。合标识符规则，并且必须在数据库中惟一。 2.table|view：是在其上建立触发器的表或视是在其上建立触发器的表或

39、视 图，亦称为触发器表或触发器视图。图，亦称为触发器表或触发器视图。 3.WITH ENCRYPTION：用于加密用于加密 syscomments 表中包含表中包含 CREATE TRIGGER 语句文本的条目，以防止将触发语句文本的条目，以防止将触发 器作为器作为 SQL Server 复制的一部分发布。复制的一部分发布。 CREATE TRIGGER参数说明（参数说明（2/3） 4.4.FOR FOR ：用以指定触发器的类型，该关键字可用以指定触发器的类型，该关键字可 以省略。后面为以省略。后面为AFTERAFTER时表示触发器为时表示触发器为AFTERAFTER 类型，后面为类型，后面为

40、INSTEAD OFINSTEAD OF时表示触发器为时表示触发器为 INSTEAD OFINSTEAD OF类型。如果仅指定类型。如果仅指定 FOR FOR 关键字，关键字， 则默认为则默认为AFTER AFTER 触发器。触发器。 5.5.DELETE,INSERT,UPDATEDELETE,INSERT,UPDATE：指定指定 在表或视图上执行哪些数据修改语句时将激在表或视图上执行哪些数据修改语句时将激 活触发器。必须至少指定一个选项，如果指活触发器。必须至少指定一个选项，如果指 定的选项多于一个，需用逗号分隔。定的选项多于一个，需用逗号分隔。 CREATE TRIGGER参数说明（参数

41、说明（3/3） 6.IF UPDATE(column)AND|ORUPDATE(colu mn)：判定某一列或几列是否被修改。如果希望判定某一列或几列是否被修改。如果希望 仅当某一列（或几列）的数据发生变化才导致触仅当某一列（或几列）的数据发生变化才导致触 发器动作，而其他列的数据变化不会引发触发器发器动作，而其他列的数据变化不会引发触发器 执行，则可以采用本选项。此时可以称之为列级执行，则可以采用本选项。此时可以称之为列级 触发器。触发器。 INSERT触发器触发器 n当在触发器表上进行插入操作时，该触发器自 动执行 n例 CREATE TRIGGER my_trigger1 ON auth

42、ors FOR INSERT AS raiserror(unauthorized,10,1) n说明：说明： n由于创建时，仅指定了由于创建时，仅指定了 FOR 关键字，因此关键字，因此 AFTER 是默认设置。这样只有在触发是默认设置。这样只有在触发SQL语句中指定的所语句中指定的所 有操作都已成功执行后才激发。对于本例，可以用有操作都已成功执行后才激发。对于本例，可以用 insert语句向语句向authors中插入元组，只是插入完成中插入元组，只是插入完成 后会出现错误信息。后会出现错误信息。 INSTEAD OF选项选项 n例 CREATE TRIGGER my_trigger2 ON

43、authors INSTEAD OF DELETE AS raiserror(You have no right to delete the record,10,1) n说明：说明： n由于使用由于使用instead of选项，在表选项，在表authors上的删除上的删除 操作将不能成功。操作将不能成功。 UPDATE触发器触发器 n在带有UPDATE触发器的表上执行UPDATE语句时被触 发。 n带有IF UPDATE选项，当特定列被 更新时触发触发器。 n例1 CREATE TRIGGER member_trigger ON authors FOR UPDATE AS IF UPDATE(

44、au_lname) BEGIN raiserror(unauthorized,10,1) rollback transaction END n对au_lname字段进行更新 nUPDATE authors nSET au_lname=Wangti nWHERE au_lname=White n该更新将失败该更新将失败 n对au_fname字段进行更新 nUPDATE authors nSET au_fname=Wangti nWHERE au_fname=Johnson n对未保护字段的更新可以进行对未保护字段的更新可以进行 DELETE触发器触发器 n由DELETE操作出发 n例 nCREA

45、TE TRIGGER delete_trigger nON sales nFOR DELETE nAS nBEGIN n raiserror(unauthorized,10,1) n rollback transaction nEND 查看触发器信息查看触发器信息 n使用系统存储过程： nsp_help：显示触发器对象的类型 nsp_helptext：显示触发器的定义文本 nsp_helptrigger：显示表中触发器的信息 nsp_depends：显示表中触发器的依赖关系 n使用系统表： nSELECT * FROM Sysobjects WHERE type=TR n使用Managemen

46、t Studio 使用触发器使用触发器 n使用触发器强制数据完整性 n与CHECK约束相比，使用触发器可以实现更加复杂 的业务逻辑，能够实现前者无法完成的功能。在以 下场合考虑使用触发器： n一个表中的一列参照另一个表中的一列，但这两列 的类型不一致； n一个表中的一列参照多个表中的多列； n应用程序需要使用自定义信息和较为复杂的错误处 理 使用带有提醒电子邮件的触发器使用带有提醒电子邮件的触发器 nUSE pubs nIF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) nDROP TRIGGE

47、R reminder nGO nCREATE TRIGGER reminder nON titles nFOR INSERT, UPDATE, DELETE nAS nEXEC master.xp_sendmail MaryM, n Dont forget to print a report for the distributors. nGO 使用触发器强制业务规则使用触发器强制业务规则 n用触发器可以实现复杂的业务规则用触发器可以实现复杂的业务规则 n例1 CREATE TRIGGER delete_customers ON Customers FOR DELETE AS IF EXISTS

48、(SELECT COUNT(*) FROM Orders INNER JOIN Deleted ON Orders.CustomerID=Deleted.CustomerID) BEGIN raiserror(You can not delete the customer with the order record the transaction will be canciled,10,1) ROLLBACK TRANSACTION END 利用触发器实现利用触发器实现参照完整性参照完整性 publishers表的主键是表的主键是pub_id，它也出现在，它也出现在 titles表中，即表中，

49、即titles表中的表中的pub_id是外键，也就是外键，也就 是说，是说，publishers为主表，而为主表，而titles则为从表。则为从表。 要维护两表间的完整性，可对要维护两表间的完整性，可对titles用外键限制或用外键限制或 参照限制来维护它们间的完整性，或对参照限制来维护它们间的完整性，或对 publishers和和titles用触发器来维护它们间的完用触发器来维护它们间的完 整性。整性。 由删除操作激发的主表由删除操作激发的主表“删除触发器删除触发器” nCREATE TRIGGER pub_del nON publishers nFOR DELETE nAS nIF row

50、count=0 nRETURN nDELETE titles nFROM titles t, deleted d nWHERE t.pub_id=d.pub_id nRETURN 由修改操作激发的主表由修改操作激发的主表“修改触发器修改触发器” nCREATE TRIGGER pub_updateCREATE TRIGGER pub_update nON publishers FOR UPDATE ASON publishers FOR UPDATE AS ndeclare num_rows intdeclare num_rows int / /* *定义局部变量定义局部变量* */ / ns

51、elect num_rows=rowcountselect num_rows=rowcount / /* * 局部变量赋值局部变量赋值 * */ / nIF num_rows=0 RETURNIF num_rows=0 RETURN nIF update(pub_id) /IF update(pub_id) /* *检查检查pub_idpub_id的值是否修改，返回逻辑值的值是否修改，返回逻辑值* */ / begin begin if num_rows 1if num_rows 1 nbeginbegin n raiserror raiserror 33333 33333 不支持主键的多行更

52、新！不支持主键的多行更新！ n rollback transactionrollback transaction n return return nendend UPDATE titles /UPDATE titles /* *级联修改级联修改titlestitles表表* */ / SET pub_id = i.pub_idSET pub_id = i.pub_id nFROM titles t, deleted d, inserted iFROM titles t, deleted d, inserted i nWHERE t.pub_id=d.pub_idWHERE t.pub_id=d

53、.pub_id n end end nRETURNRETURN 修改和删除触发器修改和删除触发器 n修改触发器语法： ALTER TRIGGER trigger_name ON table | view FOR | AFTER | INSTEAD OF INSERT ， DELETE ， UPDATE WITH ENCRYPTION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n sql_statement .n 由插入和修改操作激发的从表由插入和修改操作激发的从表“插入修改触发器插入修改触发器” nCREATE TRIGGER ti

54、tle_iuCREATE TRIGGER title_iu nON titles FOR INSERT, UPDATE ON titles FOR INSERT, UPDATE nASAS ndeclare num_rows intdeclare num_rows int nselect num_rows=rowcountselect num_rows=rowcount nIF num_rows=0 RETURNIF num_rows=0 RETURN IF (select IF (select count(count(* *) ) from publishers p, inserted If

55、rom publishers p, inserted I n where p.pub_id=i.pub_id) != num_rows where p.pub_id=i.pub_id) != num_rows n begin begin n raiserror raiserror 33334 33334 试图插入试图插入/ /更新非法更新非法 pub_idpub_id到到titlestitles表中！表中！ n rollback transactionrollback transaction n return return n end end nRETURNRETURN n删除触发器： nDR

56、OP TRIGGER trigger_name n禁止或启用触发器： ALTER TABLE talbe_name ENABLE|DISABLE TRIGGER ALL|trigger_name, 增删改增删改3种操作对主从表间数据完整性的影响种操作对主从表间数据完整性的影响 学号姓名班号 0101张三计0001 0201李四计0002 班号班名人数 计0001计算机01班60 计0002计算机02班65 学生表（从表）班级表（主表） 操作 主表- 从表从表- 主表 有无影响采取的策略有无影响采取的策略 插入操作 主表中主键值的插入 不会影响从表的外键 值 无可能有 插入从表的外键值 应“参照

57、”主表的 主键值 修改操作 主表中主键值的修改 可能会影响从表的外 键值 级联修改：Cascade 可能有 修改从表的外键值 应“参照”主表的 主键值禁止修改：No Action 删除操作 主表中主键值的删除 可能会影响从表的外 键值 级联删除：Cascade 无无 禁止删除：No Action 用户自定义函数的基本概念用户自定义函数的基本概念 n概念： n在SQL SERVER中将一个或多个T-SQL语句组成一个可以 有返回值的例程。 n根据返回值的不同分为三类： n标量函数 n内嵌表值函数 n多语句表值函数 创建用户自定义函数创建用户自定义函数 n创建语法: nCREATE FUNCTIO

58、N function_name n(parameter_name scalar_parameter_datatype n=default,n) nRETURN scalar_parameter_datatype nWITH , nAS BEGIN nSql_statment nRETURN scalar_expression nEND 创建自定义函数示例创建自定义函数示例 nCREATE FUNCTION my_function1(moneyinput money) nreturns nvarchar(5) nBEGIN n DECLARE returnstring nvarchar(5) n IF moneyinput10000 n SET returnstring=非热销商品 n ELSE n SET returnstring=热销商品 n RETURN returnstring nEND 查看用户自定义函数查看用户自定义函数 n使用系统存储过程： nsp_help nsp_helptext n使用信息表或系统信息架构视图： n使用系统表sysobjects和syscomments n使用信息架构视图：ROUTINES、PARAMETERS和 ROUTINE_COLUMNS n使用企业管理器 用户自定义函数类型用户自定义函数类型 n标量函数： n返回单个数据值 n内