《触发器与存储过程》PPT课件.ppt

1、第七章 触发器与存储过程,学习目标,掌握触发器的类型； 掌握触发器创建，修改和删除； 掌握存储过程的创建和使用。,学习重点,触发器的创建； inserted表和deleted表的使用； 存储过程的创建。,7.1 触发器概述,触发器是一种特殊的存储过程。 存储过程的执行是通过存储过程的名称被调用而执行的；触发器是通过定义的事件发生而被执行的。 存储过程是用户主动调用的，触发器是被动执行的。,7.1 触发器概述,当在指定表中使用UPDATE、INSERT 或 DELETE中一种或多种数据修改操作对数据进行修改时，触发器会生效。 SQL Server 2000将触发器和触发它的语句作为一个事务对待，

2、若条件不满足，可以回滚整个事务。 触发器主要用于强制复杂的业务规则或要求。,7.1 触发器概述,触发器的优点如下： 1触发器是被动的：它们在对表的数据作了任何修改之后立即被激活。 2触发器可以通过数据库中的相关表进行级联更改。 3触发器可以强制限制，这些限制比用CHECK 约束所定义的更复杂。,7.1 触发器概述,根据触发器的执行顺序，可将触发器分为：FOR/AFTER触发器和INSTEAD OF触发器。 FOR/AFTER触发器：都是在触发触发器的INSERT ，UPDATE和DELETE语句执行完成之后执行的。 INSTEAD OF触发器：不执行触发触发器的操作（INSERT ，UPDAT

3、E和DELELTE语句），只完成触发的动作。即用触发器中定义的操作替代触发操作。,7.1 触发器概述,根据触发触发器的操作，又可分为：INSERT触发器，UPDATE触发器和DELETE触发器。 这三种触发器不是严格划分的，一个触发器可以是任意一种，或是两种甚至三种的组合。,7.2 创建触发器,创建触发器的T-SQL语句为CREATE TRIGGER，其语法为： CREATE TRIGGER trigger_nameON tablename |viewname WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE A

4、S IF UPDATE () AND | OR UPDATE (column_name ) .n sql_statement .n ,7.2 创建触发器,例7.1当向Stu表添加记录完成时，返回一条信息“操作完成”。 USE students GO CREATE TRIGGER tri_stu ON Stu FOR INSERT AS PRINT操作完成 验证触发器是否能正常工作，向Stu表添加一条记录。 INSERT Stu VALUES (王二,女,1990-10-3计算机软件,1001,NULL),7.2 创建触发器,定义触发器的注意事项： 1CREATE TR

5、IGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 2触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。 3触发器中不允许使用下列T-SQL语句： ALTER DATABASE DISK RESIZE CREATE DATABASE DISK INIT DROP DATABASE LOAD DATABASE RECONFIGURE LOAD LOG RESTORE LOG RESTORE DATABASE,7.3 管理触发器,修改触发器的T-SQL语句是：ALTER TRIGGER，其语法格式为： ALTER TRIGGER trigger_nameON t

6、ablename |viewname WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n sql_statement .n ,7.3 管理触发器,例72修改例1中创建的触发器，将其改为当对Stu表进行插入（INSERT），修改（UPDATE）和删除（DELETE）时，都显示完成信息。 USE students GO ALTER TRIGGER tri_stu ON Stu FOR INSERT，UPDATE，

7、DELETE AS PRINT操作完成,7.3 管理触发器,若要使用T-SQL语句查看触发器的定义，可使用sp_helptext存储过程。其语法格式为： EXEC sp_helptext trigger_name 若要了解在某个表上存储哪些触发器可以使用存储过程sp_helptrigger，语法格式为： EXEC sp_helptrigger table_name 启用和禁用触发器的语法格式为: ALTER TABLE table_name disable|enable TRIGGER trigger_name,7.3 管理触发器,例7.3 再次修改例1中创建的触发器，将其文本内容加密。 US

8、E students GO ALTER TRIGGER tri_stu ON Stu WITH ENCRYPTION FOR INSERT,UPDATE,DELETE AS PRINT 操作完成,7.3 管理触发器,例7.4 查看附加到Stu表的触发器。 EXEC sp_helptrigger Stu 例7.5 删除触发器mes_stu。 DROP TRIGGER tri_stu,7.4 INSERTED表和DELETED表,触发器有两个重要的临时表：INSERTED表和DELETED表。 当向数据表中插入数据时，触发INSERT触发器，新插入的记录的副本存储在INSERTED表中，INSER

9、TED表与数据有相同的表结构。 当从数据表中删除数据时，触发DELETE触发器，被删除的记录的副本存储在DELETED表中。DELETED表与数据有相同的表结构。,7.4 INSERTED表和DELETED表,修改数据，可以看作是将原记录删除，插入一条新记录。因此，当在数据表中修改数据时，触发UPDATE触发器，原记录的副本存储在DELETED表中，修改后的记录的副本存储在INSERTED表中。,7.4 INSERTED表和DELETED表,例7.6 定义一个触发器，课程号（CouID）以“B”开头为选修课，则学生成绩（Score）低于60分的不录入。 USE students GO CREA

10、TE TRIGGER tri_BScore ON Score FOR INSERT AS IF (SELECT Score FROM INSERTED WHERE CouID Like B%)60 BEGIN ROLLBACK TRAN PRINT 该成绩不能录入 END,7.4 INSERTED表和DELETED表,例7.7 定义一个触发器，每次从学生表（Stu）中删除学生时，一次只能删除一人。 USE students GO CREATE TRIGGER tri_deletestu ON Stu FOR DELETE AS IF (SELECT COUNT(*) FROM DELETED)

11、1 BEGIN PRINT一次只能删除一名学生 ROLLBACK TRAN END,7.4 INSERTED表和DELETED表,例7.8当对Scores表的Score字段进行更新时，取消更新操作，并显示信息“学生成绩不能被修改，请与教务处联系”。 USE students GO CREATE TRIGGER tri_sc ON Scores FOR UPDATE AS IF UPDATE（Score） BEGIN ROLLBACK TRAN PRINT 学生成绩不能被修改，请与教务处联系 END,7.4 INSERTED表和DELETED表,例7.9 定义一个触发器，不允许将学生的成绩修改为

12、100分或60分。 USE students GO CREATE TRIGGER tri_update_score ON Scores FOR UPDATE AS IF （SELECT Score FROM INSERTED ）=100 OR （SELECT Score FROM INSERTED ）=60 BEGIN PRINT不能将学生的成绩修改为100分或60分 ROLLBACK TRAN END,7.5 触发器应用,INSTEAD OF触发器包含替代原操作（INSERT，UPDATE，DELETE）语句的代码。通常用于在不能直接更新的视图上，替代原操作，使其更新。在用户看来是直接在不可

13、更新的视图上更新了。,7.5 触发器应用,定义一个基于Stu表和Dep表的视图。 USE students GO CREATE VIEW stu_dep AS SELECT StuID,StuName,DepName FROM Stu AS S INNER JOIN Dep AS D ON S.DepID=D.DepID GO,7.5 触发器应用,例7.10 能够直接删除视图stu_dep中的记录。 1定义触发器 USE students GO CREATE TRIGGER tri_stu ON stu_dep INSTEAD OF DELETE AS DELETE Stu WHERE Stu

14、ID IN（SELECT StuID FROM DELETED） GO,7.5 触发器应用,2使用删除语句 DELETE stu_dep WHERE StuID=20070105 3查看结果 SELECT * FROM stu_dep,7.5 触发器应用,例.11 当某个学生从Stu表中被删除时，同时也删除Scores表中该学生的相关记录。 USE students GO CREATE TRIGGER tri_ss ON Stu FOR DELETE AS DELETE Scores WHERE StuID IN (SELECT StuID FROM DELETED) GO,7.5 触发器应用

15、,例.12 当一个学生毕业即将其从Stu表中删除时，确保该学生未有课程不及格。 USE students GO CREATE TRIGGER tri_score ON Stu FOR DELETE AS IF 60 ALL(SELECT Score FROM Scores AS S INNER JOIN DELETED AS D ON S.StuID=D.StuID) BEGIN RAISERROR(该学生还有课程未及格！,16,1) ROLLBACK TRANSCATION END GO,7.5 触发器应用,RAISERROR ( msg_id | msg_str , severity ,

16、state 说明: msg_id:存储于 sysmessages 表中的用户定义的错误信息。用户定义错误信息的错误号应大于 50,000。由特殊消息产生的错误是第 50,000 号。 Severity:用户定义的与消息关联的严重级别。用户可以使用从 0 到 18 之间的严重级别。,7.6 存储过程,存储过程(stored procedure)是一组经过预先编泽的SQL代码，存放在服务器中。用户可以调用一个单独的存储过程得到相应的返回值，从而完成一系列的操作。,7.6 存储过程,2存储过程的优点 （1）使用存储过程可以降低网络的流量 （2）提高了性能 （3）存储过程允许用户进行模块化的程序设计

17、用户可以很快的创建一个存储过程，然后把它存储到数据库中，并可在自己的应用程序中多次调用它。 （4）存储过程还可以作为安全机制的一部分,存储过程的分类 系统存储过程 由系统定义，存放在master数据库中 类似C语言中的系统函数 系统存储过程的名称都以“sp_”开头或”xp_”开头 用户自定义存储过程 由用户在自己的数据库中创建的存储过程 类似C语言中的用户自定义函数,7.6 存储过程,7.6 存储过程-常用的系统存储过程 4-1,EXEC sp_databases EXEC sp_renamedb Northwind,Northwind1 USE stuDB GO EXEC sp_tables

18、 EXEC sp_columns stuInfo EXEC sp_help stuInfo EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks EXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures,7.6 存储过程-常用的系统存储过程 4-2,修改数据库的名称(单用户访问),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表stuInfo的信息,查看表stuInfo的约束,查看表stuMarks的索引,查看视图的语句文本,查看

19、当前数据库中的存储过程,常用的扩展存储过程：xp_cmdshell 可以执行DOS命令下的一些的操作 以文本行方式返回任何输出 调用语法： EXEC xp_cmdshell DOS命令 NO_OUTPUT,7.6 存储过程-常用的系统存储过程 4-3,7.6 存储过程-常用的系统存储过程 4-4,USE master GO EXEC xp_cmdshell mkdir d:bank, NO_OUTPUT IF EXISTS(SELECT * FROM sysdatabases WHERE name=bankDB) DROP DATABASE bankDB GO CREATE DATABASE

20、bankDB ( ) GO EXEC xp_cmdshell dir D:bank -查看文件,创建数据库bankDB，要求保存在D:bank,创建文件夹D:bank,查看文件夹D:bank,7.7创建存储过程,创建存储过程语法格式： CREATE PROC EDURE procedure_name AS sql_statements 系统存储过程带有 “sp_”前缀，用户自定义的存储过程名称一般以prc作为前缀。,7.7创建存储过程,存储过程不会自动执行，执行存储过程的方法有： （1）EXECUTE 存储过程名； （2）EXEC 存储过程名； （3）存储过程名。,7.7创建存储过程,例7.1

21、3创建一个存储过程，它将显示出特定学生的信息。 CREATE PROCEDURE prc_DisplayStudentDetails iStuID int AS BEGIN PRINT convert(char,iStuID)+details SELECT * FROM stu WHERE StuID=iStuID END,7.7创建存储过程,执行该存储过程，查找学号为“20070103”学生的信息。 exec prc_DisplayStudentDetails 20070103,7.7创建存储过程,若存储过程的查询条件是可变的，根据用户在执行该存储过程时，由用户输入来决定。 语法格式： CR

22、EATE PROC EDURE procedure_name parameter data_type AS sql_statements 参数：作为存储过程与调用它的对象之间交换数据的一种方法。,7.7创建存储过程,参数的类型： 输入参数它用来接收调用者向存储过程传递(单向)的数据。 输出参数用来向调用者返回一个或多个数据。,7.7创建存储过程,从存储过程中返回数据 1用return语句返回值 2利用输出参数返回多个值,7.7创建存储过程,例7.14编写一个存储过程，根据学号显示该生的详细信息，如果没有找到返回值10。 CREATE PROCEDURE prc_SearchStudent st

23、udentid int AS BEGIN PRINT STUDENT DETAILS SELECT * FROM stu WHERE StuID=studentid IF(rowcount=0) RETURN 10 END,7.7创建存储过程,执行该存储过程 DECLARE count int exec count= prc_SearchStudent 20070111 PRINT count=+convert(char,count),7.7创建存储过程,要从存储过程中返回的信息不止一个值，选择用“输出参数”来实现。 语法格式： CREATE PROCEDURE procedure_name

24、parameter datatypeOUTPUT,n AS Sql_statements,7.7创建存储过程,例7.15创建一个存储过程，当学生要转班的时候，输入学生的学号和要转到的班级号，修改stu表中的Class，存储过程执行完后返回学生的姓名(StuName)、性别(Sex)。,7.7创建存储过程,CREATE PROCEDURE prc_UpdateClass iStuID int,cClass char(16), cStuName char(10)OUTPUT,cSex char(2) OUTPUT AS BEGIN IF EXISTS(SELECT * FROM stu WHERE

25、 StuID=iStuID) BEGIN UPDATE stu SET Class=cClass WHERE StuID=iStuID SELECT cStuName=StuName,cSex=Sex FROM stu WHERE StuID=iStuID RETURN 0 END ELSE RETURN 1 END,7.7创建存储过程,执行带参数的存储过程 DECLARE name char(10) DECLARE sex char(2) DECLARE result int EXEC result=prc_UpdateClass 20070110, 05市场营销,name OUTPUT,s

26、ex OUTPUT IF(result=0) BEGIN PRINT THE STUDENT DETAILS PRINT NAME:+name PRINT SEX:+sex END ELSE PRINT NOT FOUND!,7.7创建存储过程,例7.16创建一存储过程，它将分行显示存储过程prc_UpdateClass中返回的信息。 CREATE PROCEDURE prc_Executeprc iStuID int,cClass char(16) AS BEGIN DECLARE name char(10) DECLARE sex char(2) DECLARE result int EXEC result=prc_UpdateClass iStuID, cClass, name OUTPUT, sex OUTPUT,7.7创建存储过程,IF(result=0) BEGIN PRINT THE STUDENT DETAILS PRINT NAME:+name PRINT SEX:+sex END ELSE PRINT NOT FO