下载文档原格式
第7章触发器和存储过程数据完整性约束是指保证数据库中的数据符合现实中的实际情况，或者说，数据库中存储的数据要有实际意义。
我们在第4章介绍了在定义关系表时实现数据的完整性约束的方法，包括实体完整性、参照完整性和用户定义的完整性约束三个方面，本章我们将介绍复杂的数据完整性约束实现方法——触发器。
存储过程是SQL 语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。
存储过程使得对数据库的管理和操作更加容易，并且可以提高数据的操作效率。
7.1 触发器触发器是一段由对数据的更改操作引发的自动执行的代码，这些更改操作包括UPDATE、INSERT 或DELETE。
触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。
触发器可以实现比CHECK约束更复杂的数据约束。
从第4章的例子我们可以看到，CHECK约束只能约束位于同一个表上的列之间的取值约束，比如“最低工资小于等于最高工资”，如果被约束的列位于两个不同表中，比如，假设有职工表（职工号，姓名，工作编号，工资）和工作表（工作编号，最低工资，最高工资），如果要求职工的工资在工作表中相应工作的最低工资和最高工资范围内，这样的约束CHECK就无能为力了，这种情况就需要使用触发器来实现。
触发器是定义在某个表上的，用于限制该表中的某些约束条件，但在触发器中可以引用其它表中的列。
例如，触发器可以使用另一个表中的列来比较插入或更新的数据是否符合要求。
7.1.1创建触发器建立触发器时，要指定触发器的名称、触发器所作用的表、引发触发器的操作以及在触发器中要完成的功能。
建立触发器的SQL语句为：CREATE TRIGGER ，其语法格式为：CREATE TRIGGER 触发器名称ON {表名 | 视图名}{ FOR | AFTER | INSTEAD OF }{ [ INSERT ] [ , ] [ DELETE ] [ , ] [UPDATE ] }ASSQL 语句其中：●触发器名称在数据库中必须是惟一的。
《数据库原理与应用》实验报告题目：实验七：存储过程与触发器的应用学号：1148028姓名：沈宇杰日期：2013/5/30实验预习情况：一：实验目的：①：掌握创建存储过程的方法和步骤②：掌握存储过程的使用方法③：掌握创建触发器的方法和步骤④：掌握触发器的使用方法二：实验内容：①：存储过程的创建、执行和删除②：触发器的创建、执行和删除三：实验示例：一：创建触发器：对于STUDENT数据库，表STUDENT的CLASS_ID列与表CLASS的CLASS_ID满足下列参照完整性规则：7.1创建触发器A.向STUDENT插入或修改一记录时，该字段在STUDENT表中的对应值也做相应的修改B.修改CLASS表的CLASS_ID字段值时，该字段在STUDENT表中对应值也做相应的修改C.删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在STUDENT表中对应的记录在查询分析器编辑窗口输入下列触发器的代码并执行。
①：向STUDENT表中插入或修改一记录时，通过触发器检查记录的CLASS_ID值在CLASS表中是否存在，若不存在，则取消插入或删除操CREATE TRIGGER STUDENTINS ON DBO.STUDENTFOR INSERT,UPDATEAS BEGINIF(SELECT INS.CLASS_ID FROM inserted INS)NOT IN(SELECT CLASS_ID FROM CLASS)ROLLBACKENDGOAFTER指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。
所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
如果仅指定FOR关键字，则AFTER为默认值。
不能对视图定义AFTER触发器。
注意：书上的P162页的语法和SQL SERVER2008中的语法可能不大一样，SQL SERVER2008中并没有BEFORE、FOR EACH ROW等关键字。
信息工程学院实验报告课程名称：《数据库原理》实验项目名称：存储过程与触发器一、实验目的:（1）了解存储过程的概念（2）掌握创建、执行存储过程的方法（3）了解查看、修改和删除存储过程的方法（4）了解触发器的概念（5）掌握创建触发器的方法（6）掌握查看、修改、删除触发器信息的方法二、实验设备与器件Win7 +Sql server 2008三、实验内容与步骤（一）存储过程运行实验四附录中的SQL语句，准备实验数据。
然后创建下列存储过程，并调试运行存储过程，查看运行结果。
1．在企业管理器中创建一个名为StuInfo的存储过程，完成的功能是在student表中查询系号为D2的学号、姓名、性别、年龄、系号的内容。
CREATE PROCEDURE StuInfoASSELECT SNO AS学号,SNAME AS姓名,SSEX AS性别,SAGE AS年龄,DNO AS系号FROM studentWHERE DNO='D2'结果：stuinfo2．使用T_SQL语句创建存储过程，完成的功能是在表student,course和study中查询以下字段：学号、姓名、性别、课程名称、考试分数。
use mydb--查询是否已存在此存储过程，如果存在，就删除它if exists(select name from sysobjectswhere name='StuScoreInfo'and type='P')drop procedure StuScoreInfogo--创建存储过程CREATE PROCEDURE StuScoreInfoasselect student.sno as学号,sname as姓名,ssex as性别,ame as课程名称,study.grade as考试分数from student,course,studywhere student.sno=study.sno and o=o结果：StuScoreInfo3．使用T_SQL语句创建一个带有参数的存储过程stu_sno_info，该存储过程根据传入的学生编号，在student表中查询此学生的信息。
实验七存储过程
实验7存储过程
1，实验要求和目的
1，掌握创建存储过程的基本方法
2。
实验完成后写一份实验报告。
要求:在实验报告的“实验源代码和结果”一节中填写T-SQL语句代码和执行结果
2，实验内容
1，根据要求创建存储过程:
(1)创建存储过程，用于执行后检索学生的基本信息(检索字段有:学号、姓名、系)
(2)创建一个存储过程，该过程可以在执行后检索数据库分数大于60分的所有学生的学号和姓名。
(3)创建一个存储过程，可以执行该过程来查询任何学生编号的学生姓名、性别、年龄和系。
(4)创建存储过程时，任何记录都可以在执行过程中插入到“学生基本信息表”(表名定制)中。
(5)创建一个可执行的存储过程以输出:参加任何课程的学生人数2。
存储过程修改(使用ALTER关键字):
(1)修改问题(2)中的存储过程，修改后的存储过程可以检索数据库分数大于80分的所有男生的学生编号和姓名
(2)修改问题(3)中存储过程，修改后的存储过程可以更新学习
的任何学生的姓名(注意:update关键字用于更新表中的数据。
具体格
式见教科书第25页的“3.3.2数据修改”。
)
(3)本节中问题(2)的存储过程。
修改后的存储过程可以更新任何课程中任何学生的分数。
实验7 存储过程和触发器的使用
1.存储过程
①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存
储过程
USE YGGL
GO
CREATE PROCEDURE TEST@NU MBER int OUTPUT
AS
BEGIN
DECLARE@NU MBER2inT
SET@NU MBER2=(SELECT COUNT(*)FROM Employees)
SET@NU MBER1=@NU MBER2
END
②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1 BEGIN
DECLARE@SR1float,@SR2FLOAT
SELECT@SR1=InCome-OutCome FROM Salary WHERE EmployeeID=@ID1
SELECT@SR2=InCome-OutCome FROM Salary WHERE EmployeeID=@ID2
IF@ID1>@ID2
SET@BJ=0
ELSE
SET@BJ=1
END
③创建添加职员记录的存储过程EmployeeAdd
USE YGGL
GO
CREATE PROCEDURE EmployeeAdd
(
@employeeid char(6),@name char(10),@education char(4),@birthday datetime, @woekyear tinyint,@sex bit,@address char(40),@phonenumber char(12),
@departmentID char(3)
)AS
BEGIN
INSERT INTO Employees
VALUES(@employeeid,@name,@education,@birthday,@woekyear,
@sex,@address,@phonenumber,@departmentID)
END
RETURN
GO
④创建带有OUTPUT游标参数的存储过程，在Employees表中声明并打开游标USE YGGL
GO
CREATE PROCEDURE em_cursor@em_cursor cursor VARYING OUTPUT
AS
BEGIN
SET@em_cursor=CURSOR FORWARD_ONLY STATIC
FOR
SELECT*FROM Employees
OPEN@em_cursor
END
GO
⑤创建存储过程，使用游标确定一个员工的实际收入是否排在前三名，结果为1表示是，
结果为0表示否
REATE PROCEDURE TOP_THREE@EM_ID char(6),@OK bit OUTPUT
AS
BEGIN
DECLARE@X_EM_ID char(6)
DECLARE@ACT_IN int,@SEQ int
DECLARE SALARY_DIS cursor FOR
SELECT EmployeeID,InCome_OutCome
FROM Salary
ORDER BY InCome_OutCome DESC
SET@SEQ=0
SET@OK=0
OPEN SALARY_DIS
FETCH SALARY_DIS INTO@X_EM_ID,@ACT_IN
WHILE@SEQ<3 AND@OK=0
BEGIN
SET@SEQ=@SEQ+1
IF@X_EM_ID=@EM_ID
SET@OK=1
FETCH SALARY_DIS INTO@X_EM_ID,@ACT_IN
END
CLOSE SALARY_DIS
DEALLOCATE SALARY_DIS
END
2.触发器
①向Employees表插入或修改一个记录时，通过触发器检查记录的DpartmentID值在
Dpartments表中是否存在，若不存在，则取消插入或修改操作
USE YGGL
GO
CREATE TRIGGER EmployeesIns ON
FOR INSERT,UPDATE
AS
BEGIN
IF((SELECT DepartmentID from inserted)NOT IN
(SELECT DepartmentID FROM DepartmentS))
ROLLBACK
END
②修改Dpartments表“DpartmentID”字段值时，该字段在Employees表中对应的值也做
相应修改
USE YGGL
USE YGGL
GO
CREATE TRIGGER DepartmentUpdate ON
FOR UPDATE
AS
BEGIN
UPDATE Employees
SET DepartmentID=(SELECT DepartmentID FROM insered)
WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
GO
③删除Dpartment表中记录的同时删除该记录“DpartmentID”字段值在Employees表中对
应的记录
CREATE TRIGGER DepartmentsDelate ON
FOR DELETE
AS
BEGIN
DELETE FROM Employees
WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
GO
④创建INSTEAD OF 触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在
Employees中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”CREATE TRIGGER EM_EXISTS ON Salary
INSTEAD OF INSERT
AS
BEGIN
DECLARE@EmployeeID char(6)
SELECT@EmployeeID=EmployeeID
FROM inserted
IF(@EmployeeID IN(SELECT EmployeeID FROM Employee))
INSERT INTO Salary SELECT*FROM inserted
ELSE
PRINT'员工号不存在'
END
⑤创建DDL触发器，当删除YGGLXJ数据库的一个表时，提示“不能删除表”，并回滚删除
表的操作
USE YGGL
GO
CREATE TRIGGER table_delete
ON DATABASE
AFTER DROP_TABLE
AS
PRINT'不能删除该表'
ROLLBACK TRANSACTION。
