实验九触发器创建及使用.doc

实验九 触发器的创建和使用
9.1 概述
9.1.1 任务一 理解触发器的概念
触发器与存储过程是SQL Server数据库中的两类数据库对象，触发器是一种特殊类
型的存储过程。虽然它们都是由Transact-SQL语句编写而成，但是两者有不同之处：存储过程是由用户根据需要调用执行，而触发器则是由某个数据修改动作触发而自动执行的。另外，存储过程不依赖于某个表而单独存在，而触发器则必须依赖于一个表或视图。
9.1.2 任务二 理解触发器的优点
触发器在商业上的用途很广，对于企业来说，触发器能够使运营过程自动化。例如
在库存系统中，更新触发器能够探测到存货水平在何时达到一个特定的标准并在达到标准后自动发送一条采购信息给供应商。触发器还能够扩展SQL Server约束的逻辑完整性、默认值和规则。除此之外，触发器还有其他许多功能：
1、 相关表进行级联更改：触发器能够探测到数据库内的数据变更并自动级联影响相关表的数据，我们对某一个表的数据更新，引起该表的触发器执行，更新第2张的数据，从而引起第2个表上的触发器执行，影响第3个表的数据，实现级联更新。
2、 强化约束：触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。
3、 跟踪变化：对敏感数据的更改，可使用触发器实现详细、细致的日志记录。
9.1.3 任务三 理解触发器的类型
对应数据操作的三种基本类型：INSERT、DELETE和UPDATE，分别有三种不同类
型的触发器，在执行相应的操作时触发。一个表可以同时有多个触发器，这些触发器可以是不同类型，也可以是同一类型的。
从执行时间方面来看，SQL Server有两类触发器
Ø AFTER：触发器在触发它们的语句完成后执行。如果该语句因错误而失败，触发器将不会执行。只能为表指定该触发器，同一张数据表中可以为每个出发操作（INSERT、DELETE和UPDATE）创建多个AFTER触发器。如果一个表上有多个AFTER触发器，可使用SP_SETTRIGGERORDER定义哪个AFTER触发器最先激发，哪个最后激发。除第一个和最后一个触发器外，所有其他的AFTER触发器的加分顺序不确定，并且无法控制。
Ø Instead of：该触发器代替触发操作执行。可在表或视图上定义Instead of触发器，只能为每个触发操作（INSERT、DELETE和UPDATE）定义一个Instead of触发器。Instead of触发器可用于对INSERT和UPDATE语句提供的数据执行增强的完整性检查，Instead of触发器还运行指定某些操作，是一般不支持更新的视图可以被更新。
AFTER和INSTEAD OF触发器的功能比较
功能
AFTER触发器
INSTEAD OF触发器
适用范围
表
表和视图
可定义的数量
每个操作（INSERT、DELETE和UPDATE）可定义多个
每个操作（INSERT、DELETE和UPDATE）只定义一个
执行时机
晚于约束处理，insert表和deleted表创建时触发动作
早于约束处理，insert表和deleted表创建之后触发动作
执行顺序
可指定第一个和最后一个执行
不可用
在insert表和deleted表引用text、ntext、image类型
不允许
允许
9.2 创建触发器
创建触发器之前需要注意：
Ø 触发器为数据库对象，其命名规则应与标识符命名规则一致。
Ø CREATE TRIGGER必须是批处理中的第一天语句。
Ø 只能在当前的数据库中创建，但可以引用其他数据库的对象。
Ø 不能在临时表或系统表上创建触发器。
Ø 在触发器内部SQL语句与导致该触发器执行的SQL语句属于同一事务，因此，如触发器执行不成功，则整个（INSERT、DELETE和UPDATE）SQL将回滚。
Ø 在同一条CREATE TRIGGER语句中，可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。
Ø 如果一个表的外键在DELETE/UPDATE操作上定义了级联，则不能在该表上定义INSTEAD OF DELETE/UPDATE触发器。
可使用企业管理器或者CREATE TRIGGER语句创建触发器
9.2.1 任务一 使用企业管理器创建触发器
9.2.2 任务二 使用Transact-SQL语句创建触发器
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
} ]
sql_statement [ ...n ]
}
}
v trigger_name：是触发器的名称 。
v Table | view：是在其上执行触发器的表或视图 。
v AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。如果仅指定 FOR 关键字，则 AFTER 是默认设置。
v INSTEAD OF：指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。
v AS：是触发器要执行的操作。
v sql_statement：是触发器的条件和操作。
v IF UPDATE (column)：测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作 。
1、deleted 表和 inserted 表
v deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的备份。在执行 DELETE 或 UPDATE 语句时，行从基本表中删除，并转移到 deleted 表中。deleted 表和基本表通常没有相同的行。
v inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的备份。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和基本表中。inserted表中的行是基本表中新行的备份。
UPDATE操作相当于首先执行DELETE操作，然后执行INSERT操作，因此要用到deleted 表和 inserted 表。

1． 触发器的创建和使用。
（1）、在查询分析器中使用SQL语句在学生信息表（Students）创建一个名为“trig_1”的触发器，当向学生表添加记录时，该触发器自动显示学生表的所有信息。
SQL语句如下：
CREATE TRIGGER trig_1 ON Students
FOR INSERT
AS
BEGIN
SELECT *
FROM Students
END
（2）、在查询分析器中使用SQL语句在学生信息表（Students）创建一个名为“trig_2”的触发器，当试图向学生表添加、修改或删除记录时，该触发器自动显示如下信息：“对不起，你无权进行更新操作！”。
SQL语句如下：
CREATE TRIGGER trig_2 ON Students
INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
PRINT '对不起，你无权进行更新操作！'
END
（3）、修改“trig_2”的触发器，当向学生表修改记录时，该触发器自动显示修改前和修改后的记录。
SQL语句如下：
ALTER TRIGGER trig_2 ON Students
FOR UPDATE
AS
BEGIN

SELECT '修改前的记录',* FROM DELETED
SELECT '修改后的记录',* FROM INSERTED
END
（4）、在查询分析器中使用SQL语句在学生信息表（Students）创建一个名为“trig_3”的触发器，当向学生表删除记录时，该触发器自动删除选课信息表（Enrollment）中与之相关的所有记录。
SQL语句如下：
CREATE TRIGGER trig_3 ON Students
FOR DELETE
AS
BEGIN

DECLARE @Sno INT
SELECT @Sno = Sno FROM DELETED
IF EXISTS(SELECT * FROM Enrollment WHERE Sno = @Sno )
BEGIN
DELETE FROM Enrollment
WHERE Sno = @Sno
END
END
（5）、在查询分析器中使用SQL语句在选课信息表（Enrollment）创建一个名为“trig_4”的触发器，当向选课信息表添加记录时，该触发器首先查询学生信息表中是否有这个人，如果有则自动显示与该记录相关的学生的学号、姓名和系别。否则提示没有这个学生，不允许插入这条记录。
SQL语句如下：
CREATE TRIGGER trig_4 ON Enrollment
FOR INSERT
AS
BEGIN
DECLARE @Sno INT
SELECT @Sno = Sno FROM INSERTED
IF NOT EXISTS(SELECT * FROM Students WHERE Sno = @Sno )
BEGIN
DELETE FROM Enrollment
WHERE Sno = @Sno
PRINT '错误，该学生不存在，请重新输入'
END
END
9.3 查看、修改和删除触发器
9.3.1 任务一 查看触发器
1．使用企业管理器查看触发器信息
（1）查看触发器定义信息。
1）展开服务器组，然后展开服务器。
2）展开“数据库”文件夹，展开含触发器的表所属的数据库，然后单击“表”文件夹。
3）在详细信息窗格中，右击触发器所在的表，指向“所有任务”菜单，然后单击“管理触发器”命令
2）查看触发器的相关性
1）展开服务器组，然后展开服务器。
2）展开“数据库”文件夹，展开触发器所属的数据库，然后单击“表”文件夹。
3）在详细信息窗格中，右击触发器所属的表，指向“所有任务”菜单，然后单击“显示相关性”命令。
4）在“对象”中，单击要查看其相关性的触发器。
2．使用系统存储过程查看触发器
sp_help trigger_name：显示触发器的所有者和创建时间。
sp_helptext trigger_name：显示触发器的源代码
sp_depends trigger_name：显示某个表定义的触发器清单
sp_helptrigger trigger_name：显示该触发器引用的对象清单
9.3.2 任务二 修改触发器
1、使用Transact-SQL语句修改触发器的正文
ALTER TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
} ]
sql_statement [ ...n ]
}
}

2、使用系统存储过程修改触发器的名称
sp_rename old_name，new_name

9.3.3 删除触发器
1.使用企业管理器删除触发器

2.使用Transact-SQL语句删除触发器
DROP TRIGGER { trigger } [ ,...n ]
v trigger：是要删除的触发器名称。

实验九 触发器的创建和使用
一、实验目的
1.     理解触发器的概念与类型。
2.     掌握创建、修改和删除触发器的方法。
3.     掌握使用触发器维护数据完整性的方法。

二、实验内容
 触发器的创建和使用。
（1）、在查询分析器中使用SQL语句在学生信息表（Students）创建一个名为“trig_1”的触发器，当向学生表添加记录时，该触发器自动显示学生表的所有信息。
（2）、在查询分析器中使用SQL语句在学生信息表（Students）创建一个名为“trig_2”的触发器，当试图向学生表添加、修改或删除记录时，该触发器自动显示如下信息：“对不起，你无权进行更新操作！”。
（3）、修改“trig_2”的触发器，当向学生表修改记录时，该触发器自动显示修改前和修改后的记录。
（4）、在查询分析器中使用SQL语句在学生信息表（Students）创建一个名为“trig_3”的触发器，当向学生表删除记录时，该触发器自动删除选课信息表（Enrollment）中与之相关的所有记录。
（5）、在查询分析器中使用SQL语句在选课信息表（Enrollment）创建一个名为“trig_4”的触发器，当向选课信息表添加记录时，该触发器首先查询学生信息表中是否有这个人，如果有则自动显示与该记录相关的学生的学号、姓名和系别。否则提示没有这个学生，不允许插入这条记录。
SQL语句如下：

在pubs数据库中，执行下来操作
1、 创建一个触发器T_Update，使其销售书籍时能自动更新其库存数量。
（假设库存数量存放于数据表Titles中的YDT_SALES；销售数据存放于SALES中）
2、 创建一个触发器，强制对EMPLOYEE表只能删除一行数据。
3、 显示在TITLES上创建的触发器。
4、 创建以触发器，用于检查对表Royalty输入的有效性，保证royalty，TITLE_ID字段不能为空。