第6章 存储过程、触发器

1、. 第6章 存储过程、触发器6.1 存储过程存储过程6.2 触发器触发器. 6.1.1存储过程的类型 (1) 系统存储过程系统存储过程 系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库统存储过程定义在系统数据库master中，其前缀是中，其前缀是sp_，例如常用的显示系统对，例如常用的显示系统对象信息的象信息的sp_help存储过程，它们为检索系统表的信息提供了方便快捷的方法。存储过程，它们为检索系统表的信息提供了方便快捷的方法。 系统存储过程允许系统管理员执行修改系统表的数据库管理任务，

2、可以在系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。常用的系统存储过程，请见附录。任何一个数据库中执行。常用的系统存储过程，请见附录。 (2) 本地存储过程本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以定数据库操作任务，其名称不能以sp_为前缀。为前缀。 (3) 临时存储过程临时存储过程 临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就

3、称为局部临时存储过程，这种存储过程只能在一个用户会，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。话中使用。 如果本地存储过程的名称前有两个如果本地存储过程的名称前有两个“#”，该过程就是全局临时存储过程，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。这种存储过程可以在所有用户会话中使用。 (4) 远程存储过程远程存储过程 远程存储过程指从远程服务器上调用的存储过程。远程存储过程指从远程服务器上调用的存储过程。 (5) 扩展存储过程扩展存储过程 在在SQL Server2000环境之外执行的动态链接库称为扩展存储过程，其前缀环境之外执行的动态链接库

4、称为扩展存储过程，其前缀是是sp_。使用时需要先加载到。使用时需要先加载到SQL Server2000系统中，并且按照使用存储过程的系统中，并且按照使用存储过程的方法执行。方法执行。. 6.1.2用户存储过程的创建与执行 用户存储过程只能定义在当前数据库中，可以使用用户存储过程只能定义在当前数据库中，可以使用SQL命令语句或命令语句或SQL Server的企业管理器创建存储过程。缺省情况下，用户创建的存储过程归数据库的企业管理器创建存储过程。缺省情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。所有者拥有，数据库的所有者可以把许可授权给其他用户。 1创建存储

5、过程创建存储过程 语法格式语法格式: CREATE PROC EDURE procedure_name ; number /*定义过程名定义过程名*/ parameter data_type /*定义参数的类定义参数的类型型*/ VARYING = default OUTPUT /*定义参数的属性定义参数的属性*/ ,.n1 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION /*定义存储过程的处理方定义存储过程的处理方式式*/ FOR REPLICATION AS sql_statement .n2 /*执行的操作执行的操作*/ 说明：说

6、明： 参数参数procedure_name用于指定存储过程名，必须符合标识符规则，且对于数用于指定存储过程名，必须符合标识符规则，且对于数据库及其所有者必须唯一；创建局部临时过程，可以在据库及其所有者必须唯一；创建局部临时过程，可以在 procedure_name 前面加前面加一个一个“#”；创建全局临时过程，可以在；创建全局临时过程，可以在 procedure_name 前加前加“#”。 参数参数number为可选的整数，用于区分同名的存储过程，以便用一条为可选的整数，用于区分同名的存储过程，以便用一条 DROP PROCEDURE 语句删除一组存储过程；语句删除一组存储过程；.6.1.2用

7、户存储过程的创建与执行 FOR REPLICATION用于说明不能在订阅服务器上执行为复制创用于说明不能在订阅服务器上执行为复制创建的存储过程，该选项不能和建的存储过程，该选项不能和 WITH RECOMPILE一起使用。参数一起使用。参数sql_statement代表过程体包含的代表过程体包含的T-SQL 语句，参数语句，参数n2说明一个存储过程说明一个存储过程可以包含多条可以包含多条 T-SQL 语句。语句。 对于存储过程要注意下列几点：对于存储过程要注意下列几点： (1) 用户定义的存储过程只能在当前数据库中创建（临时过程除外，用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时

8、过程总是在临时过程总是在 tempdb 中创建）。中创建）。 (2) 成功执行成功执行 CREATE PROCEDURE 语句后，过程名称存储在语句后，过程名称存储在 sysobjects 系统表中，而系统表中，而 CREATE PROCEDURE 语句的文本存储在语句的文本存储在 syscomments 中。中。 (3) 自动执行存储过程自动执行存储过程 QL Server 启动时可以自动执行一个或多个存储过程。这些存储过启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在程必须由系统管理员在 master 数据库中创建，并在数据库中创建，并在 sysadmin 固定服务固定

9、服务器角色下作为后台过程执行。这些过程不能有任何输入参数。器角色下作为后台过程执行。这些过程不能有任何输入参数。 (4) sql_statement的限制的限制 除了除了 SET SHOWPLAN_TEXT 和和 SET SHOWPLAN_ALL外，其外，其它它SET 语句均可在存储过程内使用。语句均可在存储过程内使用。 .6.1.2用户存储过程的创建与执行 如下语句必须使用对象所有者名对数据库对象进行限定：如下语句必须使用对象所有者名对数据库对象进行限定： CREATE TABLE 、ALTER TABLE、DROP TABLE 、TRUNCATE TABLE 、CREATE INDEX、D

10、ROP INDEX、UPDATE STATISTICS 及及DBCC语句。语句。 权限。权限。CREATE PROCEDURE的权限默认授予的权限默认授予sysadmin固定服务器固定服务器角色成员、角色成员、db_owner 和和 db_ddladmin 固定数据库角色成员。固定数据库角色成员。sysadmin 固定服务器角色成员和固定服务器角色成员和 db_owner 固定数据固定数据 库角色成员可以将库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。权限转让给其他用户。 注意：存储过程的定义不能跨越批处理。注意：存储过程的定义不能跨越批处理。 2存储过程的执行存储过

11、程的执行 通过通过EXEC命令可以执行一个已定义的存储过程。命令可以执行一个已定义的存储过程。 语法格式：语法格式： EXEC UTE return_status = procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE .6.1.2用户存储过程的创建与执行 说明：说明： 参数参数return_status为可选的整型变量，保存存储过程的返回状态，为可选的整型变量，保存存储过程的返回状态， EXECUTE语句使用该变量前，必须对其定

12、义。参数语句使用该变量前，必须对其定义。参数procedure_name 和和number用于调用已定义的一组存储过程中的某一个，用于调用已定义的一组存储过程中的某一个，procedure_name代表了存储过程的组名，代表了存储过程的组名，number用于指定组中的存储用于指定组中的存储过程。定义存储过程组的目的是以便用一条过程。定义存储过程组的目的是以便用一条DROP PROCEDURE 语句语句删除一组存储过程，对过程分组后，不能删除组中的单个过程。参数删除一组存储过程，对过程分组后，不能删除组中的单个过程。参数procedure_name_var代表存储过程名。代表存储过程名。para

13、meter为为CREATE PROCEDURE 语句中定义的参数名；语句中定义的参数名； value为存储过程的实参；为存储过程的实参；variable为变量，用于保存为变量，用于保存OUTPUT参数返回的值。参数返回的值。DEFAULT关键关键字表示不提供实参，而是使用对应的默认值。字表示不提供实参，而是使用对应的默认值。n：表示实参可有多个。：表示实参可有多个。关键字关键字WITH RECOMPILE指定强制编译。指定强制编译。 存储过程的执行要注意下列几点：存储过程的执行要注意下列几点： (1) 如果存储过程名的前三个字符为如果存储过程名的前三个字符为 sp_，SQL Server 会在

14、会在 Master 数据库中寻找该过程。如果没能找到合法的过程名，数据库中寻找该过程。如果没能找到合法的过程名，SQL Server 会寻找所有者名称为会寻找所有者名称为 dbo 的过程。的过程。 (2) 参数可以通过参数可以通过 value 或或 parameter_name = value 提供。提供。 (3) 执行存储过程时，若语句是批处理中的第一个语句，则不一定执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定要指定EXECUTE 关键字。关键字。.6.1.2用户存储过程的创建与执行 3存储过程的几种情况存储过程的几种情况 (1) 不使用任何参数的存储过程不使用任何参数的存储

15、过程 【例【例6.1】从】从XSCJ数据库的三个表中查询，返回学生学号、姓名、数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、课程名、成绩、 学分。该存储过程不使用任何参数。学分。该存储过程不使用任何参数。 /*创建存储过程创建存储过程*/ CREATE PROCEDURE student_info AS SELECT a.学号学号, 姓名姓名, 课程名课程名, 成绩成绩, t.学分学分 FROM XS a INNER JOIN XS_KC b ON a.学号学号 = b.学号学号 INNER JOIN KC t ON b.课程号课程号= t.课程号课程号 student_info 存

16、储过程可以通过以下方法执行：存储过程可以通过以下方法执行： EXECUTE student_info 或者或者 EXEC student_info 如果该过程是批处理中的第一条语句，则可使用：如果该过程是批处理中的第一条语句，则可使用： student_info.6.1.2用户存储过程的创建与执行 (2) 使用带参数的存储过程使用带参数的存储过程 【例【例6.2】从】从XSCJ数据库的三个表中查询某人指定课程的成绩和学数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。分。该存储过程接受与传递参数精确匹配的值。 CREATE PROCEDURE student_

17、info1 name char (8),cname char(16) AS SELECT a.学号学号, 姓名姓名, 课程名课程名, 成绩成绩, 学分学分 FROM XS a INNER JOIN XS_KC b ON a.学号学号 = b.学号学号 INNER JOIN KC t ON b.课程号课程号= t.课程号课程号 WHERE a.姓名姓名=name and t.课程名课程名=cname student_info1 存储过程有多种执行方式，下面列出了一部分：存储过程有多种执行方式，下面列出了一部分： EXECUTE student_info1 王林王林,计算机基础计算机基础 或者或者

18、 EXECUTE student_info1 name=王林王林, cname=计算机基础计算机基础 或者或者 EXECUTE student_info1 cname=计算机基础计算机基础, name=王林王林 或者或者 EXEC student_info1 王林王林, 计算机基础计算机基础 或者或者 EXEC au_info cname=计算机基础计算机基础, name=王林王林 .6.1.2用户存储过程的创建与执行 (3) 使用带有通配符参数的存储过程使用带有通配符参数的存储过程 【例【例6.3】从三个表的联接中返回指定学生的学号、姓名、所选课程】从三个表的联接中返回指定学生的学号、姓名、

19、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。提供参数，则使用预设的默认值。 CREATE PROCEDURE st_infoname varchar(30) = 刘刘% AS SELECT a.学号学号,a.姓名姓名,c.课程名课程名,b.成绩成绩FROM XS a INNER JOIN XS_KC b ON a.学号学号 =b.学号学号 INNER JOIN KC c ON c.课程号课程号= b.课程号课程号 WHERE 姓名姓名 LIKE name st_info 存储过

20、程可以有多种执行形式，下面列出了一部分：存储过程可以有多种执行形式，下面列出了一部分： EXECUTE st_info /*参数使用默认值参数使用默认值*/ 或者或者 EXECUTE st_info 王王% /*传递给传递给name 的实参为的实参为王王%*/ 或者或者 EXECUTE st_info 王张王张% (4) 使用带使用带OUTPUT参数的存储过程参数的存储过程.6.1.2用户存储过程的创建与执行 【例【例6.4】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。数。 CREATE PROCED

21、URE totalcredit name varchar(40), total int OUTPUT AS SELECT total= SUM(KC.学分学分)FROM XS,XS_KC,KCWHERE 姓名姓名=name AND XS.学号学号= XS_KC.学号学号 and XS_KC.课程号课程号=KC.课课程号程号 GROUP BY XS.学号学号 注意：注意： OUTPUT 变量必须在创建表和使用该变量时都进行定义。变量必须在创建表和使用该变量时都进行定义。 定义时的参数名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹。定义时的参数名和调用时的变量名不一定要匹配，不过数据

22、类型和参数位置必须匹。 DECLARE t_credit char(20),total int EXECUTE totalcredit 王林王林, total OUTPUT SELECT 王林王林, total (5) 使用使用OUTPUT游标参数的存储过程游标参数的存储过程 OUTPUT 游标参数用于返回存储过程的局部游标。游标参数用于返回存储过程的局部游标。 【例【例6.5】在】在 XSCJ数据库的数据库的XS表上声明并打开一个游标。表上声明并打开一个游标。 CREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUT AS SET

23、st_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT *FROM XS OPEN st_cursor.6.1.2用户存储过程的创建与执行 在如下的批处理中，声明一局部游标变量，执行上述存储过程过程并在如下的批处理中，声明一局部游标变量，执行上述存储过程过程并将游标赋值给局部游标变量，然后通过该游标变量读取记录。将游标赋值给局部游标变量，然后通过该游标变量读取记录。 DECLARE MyCursor CURSOR EXEC st_cursor st_cursor = MyCursor OUTPUT WHILE (FETCH_STATUS = 0) B

24、EGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor (6) 使用使用 WITH ENCRYPTION 选项选项 WITH ENCRYPTION 子句对用户隐藏存储过程的文本。子句对用户隐藏存储过程的文本。 【例【例6.6】创建加密过程，使用】创建加密过程，使用 sp_helptext 系统存储过程获取关于加系统存储过程获取关于加密过程的信息，然后尝试直接从密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。表中获取关于该过程的信息。 CREATE PROCEDURE encrypt

25、_this WITH ENCRYPTION AS SELECT * FROM XS.6.1.2用户存储过程的创建与执行 通过系统存储过程通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。用户定义函数、触发器或视图的文本。 执行如下语句：执行如下语句： EXEC sp_helptext encrypt_this 结果集为提示信息：对象备注已加密。结果集为提示信息：对象备注已加密。 (7) 创建用户定义的系统存储过程创建用户定义的系统存储过程 【例【例6.7】创建一个过程，显示表名以】创建一个过程，显

26、示表名以 xs 开头的所有表及其对应的索引。如开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以果没有指定参数，该过程将返回表名以 kc 开头的所有表及对应的索引。开头的所有表及对应的索引。 USE master GO CREATE PROCEDURE sp_showtable TABLE varchar(30) = kc% AS SELECT AS TABLE_NAME, AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes inx INNER JOIN sysobjects tab ON tab.i

27、d = inx.id WHERE LIKE TABLE GO USE XSCJ EXEC sp_showtable xs% GO.6.1.2用户存储过程的创建与执行 4利用企业管理器编辑用户存储过程利用企业管理器编辑用户存储过程 如果要通过企业管理器界面定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，步骤如下： 第第1步步 在SQL Server 企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图6.1所示的快捷菜单。 第第2步步 选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口中输入定义的存储过程，如

28、图6.2所示，然后选择“确定”按钮。图图6.1 存储过程的快捷菜单存储过程的快捷菜单 图图6.2 编辑存储过程的属性窗口编辑存储过程的属性窗口. 6.1.3用户存储过程的修改 使用ALTER PROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。 语法格式：语法格式： ALTER PROC EDURE procedure_name ; number parameter data_type VARYING 0= default OUTPUT ,.n1 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATIO

29、N AS sql_statement .n2 说明：说明： 各参数含义与CREATE PROCEDURE相同。如果原来的过程定义是用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的，那么只有在 ALTER PROCEDURE 中也包含这些选项时，这些选项才有效。 ALTER PROCEDURE 权限默认授予 sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员和过程的所有者且不可转让。.6.1.3用户存储过程的修改 用 ALTER PROCEDURE 更改后，过程的权限和启动属性保持不变。 【例【例6.8】对存储过程s

30、tudent_info1进行修改。 USE XSCJ GO ALTER PROCEDURE student_info1 name char(8),cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XS a INNER join XS_KC b ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号 WHERE a.姓名=name and t.课程名=cname GO 【例【例6.9】创建名为 select_students 的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。当该过程需更

31、改为能检索计算机专业的学生信息时，用 ALTER PROCEDURE 重新定义该存储过程。.6.1.3用户存储过程的修改USE XSCJGOCREATE PROCEDURE select_students /*创建存储过程*/AS SELECT * FROM XS ORDER BY 学号GO修改存储过程select_studentsALTER PROCEDURE select_students WITH ENCRYPTIONAS SELECT * FROM XS WHERE 专业名= 计算机 ORDER BY 学号GO.6.1.4用户存储过程的删除 当不再使用一个存储过程时，就要把它从数据库中

32、删除。使用DROP PROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。 语法格式：语法格式： DROP PROCEDURE procedure ,.n 说明：说明： procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。默认情况下，将 DROP PROCEDURE 权限授予过程所有者，该权限不可转让。 db_owner 和 db_ddladmin 固定数据库角色成员和 sysadmin 固定服务器角色成员可以通过在 DROP PROCEDURE 内指定所有者删除任何对象。 若要查看过程名列表，可使用 sp_help

33、系统存储过程。若要显示过程定义（存储在 syscomments 系统表内），可使用 sp_helptext。 【例【例6.10】删除 XSCJ数据库中的student_info1 存储过程。 USE XSCJ GO DROP PROCEDURE student_info 利用SQL命令创建触发器SQL命令创建触发器命令创建触发器语法格式：语法格式：CREATE TRIGGER trigger_name ON table | view /*指定操作对象*/ WITH ENCRYPTION /*说明是否采用加密方式*/ FOR | AFTER | INSTEAD OF INSERT

34、, UPDATE WITH APPEND NOT FOR REPLICATION /*说明该触发器不用于复制*/ AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n /*两个IF子句用于说明触发器执行的条件*/ sql_statement .n /*一条或若干条SQL语句*/ .6.2.1 利用SQL命令创建触发器 说明：说明： 参数trig

35、ger_name用于指定触发器名。触发器名必须符合标识符规则，并且在数据库中必须唯一，可以包含触发器所有者名。Table | view指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以包含表或视图的所有者名。使用关键字WITH ENCRYPTION可防止将触发器作为 SQL Server 复制的一部分发布。 bitwise_operator为用于比较运算的位运算符。updated_bitmask为整型的位屏蔽码，与实际更新或插入的列对应，例如：表t包含列 C0、C1、C2、C3 和 C4。假定该表上有 UPDATE 触发器，若要检查列 C0、C2 、C4是否都有更新，可指定upd

36、ated_bitmask的值为00010101=0 x15；若要检查是否只有列 C1 有更新，可指定updated_bitmask的值为000000102。 comparison_operator为比较运算符；column_bitmask为列屏蔽码，用来检查是否已更新或插入了对应列。参数sql_statement为触发器的T-SQL语句，当执行DELETE、INSERT 或 UPDATE操作时，对应的触发器操作将生效。n表示触发器中可以包含多条 T-SQL 语句。 2触发器中使用的特殊表触发器中使用的特殊表 执行触发器时，系统创建了两个特殊的逻辑表inserted表和deleted表，下面介绍

37、一下这两个表的内容。 inserted 逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。.6.2.1 利用SQL命令创建触发器 deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。修改一条记录等于插入一新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。触发器可检查deleted表、inserted表及被修改的表。 例如，若要检索 deleted、inserted 表中的所有记录，

38、可使用如下语句： SELECT * FROM deleted SELECT * FROM inserted deleted、inserted 逻辑表的查询方法与数据库表的查询方法相同。 3使用触发器的限制使用触发器的限制 使用触发器有下列限制： (1) CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 (2) 触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。 (3) 如果指定触发器所有者名限定触发器，要以相同的方式限定表名。 (4) 在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触

39、发器操作。 .6.2.1 利用SQL命令创建触发器 (5) 如果一个表的外键在 DELETE、UPDATE 操作上定义了级联，则不能在该表上定义 INSTEAD OF DELETE、INSTEAD OF UPDATE 触发器。 (7) 在触发器内可以指定任意的 SET 语句，所选择的 SET 选项在触发器执行期间有效 ，并在触发器执行完后恢复到以前的设置。 (8) 触发器中不允许包含以下 T-SQL 语句：CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、LOAD LOG 、RESTO

40、RE LOG 、DISK INIT、DISK RESIZE和RECONFIGURE (9) 触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含 SELECT 语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。 4权限权限 CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。.6.2.1 利用SQL命令创建触发器 【例【例6.11】对于XSCJ数据库，如果在XS表中添加或更

41、改数据，则将向客户端显示一条信息。 /*使用带有提示消息的触发器*/ USE XSCJ GO CREATE TRIGGER reminder ON XSFOR INSERT, UPDATE AS RAISERROR (4008, 16, 10) GO 消息 4008 是sysmessages 中的用户定义消息。有关创建用户定义消息的方法请参考附录的sp_addmessage存储过程。.6.2.1 利用SQL命令创建触发器 【例【例6.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。

42、USE XSCJ GO CREATE TRIGGER check_trigON XS_KCFOR INSERT AS SELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT b.学号 FROM XS b) OR a.课程号 NOT IN (SELECT c.课程号 FROM KC c) BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION END.6.2.1 利用SQL命令创建触发器 【例【例6.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并

43、取消修改操作。 通过调用 COLUMNS_UPDATED()函数，可快速测试对学号列和课程号列修改所做的更改。 USE XSCJ GO CREATE TRIGGER update_trigON XS_KC FOR update AS /*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/ IF (COLUMNS_UPDATED() & 3) 0BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION END GO.6.2.1 利用SQL命令创建触发器 5. INSTEAD OF触发器的设计触发

44、器的设计 如果视图的数据来自于多个基表，则必须使用INSTEAD OF 触发器支持引用表中数据的插入、更新和删除操作。 例如，若在一个多表视图上定义了 INSTEAD OF INSERT 触发器，视图各列的值可能允许为空也可能不允许为空，若视图某列的值不允许为空，则 INSERT 语句必须为该列提供相应的值。 如果视图的列为以下几种情况之一： （1）基表中的计算列。 （2）IDENTITY INSERT 为 OFF 的基表中的标识列。 （3）具有 timestamp 数据类型的基表列。 该视图的INSERT语句必须为这些列指定值，INSTEAD OF 触发器在构成将值插入基表的 INSERT

45、语句时会忽略指定的值。 【例【例6.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEAD OF INSERT触发器的使用。.6.2.1 利用SQL命令创建触发器 直接引用 books表的 INSERT 语句不能为BookKey字段和.ComputedCol.字段提供值，例如： -正确的INSERT语句 INSERT INTO books(BookName ,Color, Pages) VALUES (计算机辅助设计, 红色,100) -查看INSERT 语句的执行结果: SELECT * FROM books -不正确的INSERT语句 INSERT INTO books VALUES (2, 计算机辅助设计, 红色, 绿色,100) 但对于引用 View2 视图的 INSERT 语句视图的每一列都指定值，例如： -对于视图View2，正确的 INSERT 语句 INSERT INTO View2 (BookKey ,BookName ,Color, ComputedCol ,Pages)VALUES (4, 计算机辅助设计, 红色, 绿色,100)