资源简介

(共27张PPT)
WELCOME TO 数据库
商贸系
齐莉丽
E-mail: qiliru@
第七节 触发器
一、触发器的基本概念
触发器与普通存储过程的不同之处在于：触发器的执行是由事件触发的，而普通存储过程是由命令调用的。
触发器是一种特殊类型的存储过程，它在指定表中的数据发生变化时自动生效。
触发器可以实施更为复杂的数据完整性约束。使用触发器的目的是为了更好的维护企业的业务规则。
触发器主要提供一下功能：
1、触发器是自动的：它们在对表的数据进行了任何修改之后立即被激活。
2、撤销或回滚违反引用完整性的操作，防止非法修改数据。
3、执行比检查约束（CHECK）更复杂的约束操作。
4、级联修改数据库中的所有相关表。
5、在一张表的同一类型的操作（插入、更新或删除）上设置多个触发器，从而可以针对同样的修改语句执行不同的多种操作。
SQL Server系统提供了两种触发器选项：
INSTEAD OF触发器。执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。
AFTER触发器。在执行了INSERT、UPDATE、DELETE语句操作之后执行AFTER触发器。
INSTEAD OF触发器和AFTER触发器的功能比较
功能
INSTEAD OF触发器
AFTER触发器
适用范围
表和视图
表
每个表或视图含触发器数量
每个触发动作（UPDATE、DELETE和INSERT）含一个触发器
每个触发动作（UPDATE、DELETE和INSERT）含多个触发器
执行
早于：
约束处理
代替：
触发动作
晚于：
inserted和deleted表的创建
晚于：
约束处理
声明引用操作
inserted和deleted表的创建
触发动作
二、inserted和deleted表
触发器语句中使用了两种特殊的表。SQL Server系统自动创建和管理这些表。可以使用这两个表测试某些数据修改的效果及设置触发的条件，但不能直接对表中的数据进行修改。
deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE和UPDATE语句时，行从触发器表中删除，并传输到deleted表中。 deleted表和触发器表通常没有相同的行。
inserted表用于存储DELETE和UPDATE语句所影响的行的复本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。 inserted表中的行是触发器表中新行的副本。
更新事务类似于在删除之后执行插入；首先旧行被复制到deleted表中，然后新行被复制到触发器表和inserted表中。
三、创建触发器
CREATE TRIGGER trigger_name
ON table\view
{ {FOR| AFTER| INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]}
AS
sql_statement[…n]
}
trigger_name:触发器的名字。命名必须遵守SQL Server的数据库对象命名规则。
ON table\view：在哪一个表或视图上创建触发器。触发器虽然可引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
AFTER：默认的触发器类型，可以不注明。
AS：是触发器要执行的操作。
sql_statement：是触发器的条件和操作。
四、删除触发器
DROP TRIGGER trigger_name
例： DROP TRIGGER tr_product_update
五、修改触发器
ALTER TRIGGER trigger_name
六、触发器综合应用举例
例1：要在表Department中定义一个触发器DepIDChange,当表Department中的列Dep_ID被更新时，将表workers 中相应的Dep_ID列同时更新。
create table Department
(dep_id int,
DepName char(10) not null)
create trigger DepIDChange on Department
after update
as
if update(Dep_id)
begin
declare @depid as smallint
declare @old_depid as smallint
select @depid=dep_id from inserted
select @old_depid=dep_id from deleted
update workers
set workers.dep_id=@depid
where workers.dep_id=@old_depid
第八节 存储过程
一、存储过程的基本概念
存储过程是Transact-SQL语句的预编译集合，这些语句在一个名称下存储并作为一个单元进行处理。存储过程可以接受参数、返回状态值和参数值，并且可以嵌套调用。
使用存储过程能够改变Transact-SQL语句的运行性能，提高其执行效率。
在SQL Server系统中，对于某些比较固定的任务，可以将完成该任务的各种命令集中起来，写成一个新命令，执行该命令就可以完成该项任务。这种方法称为存储过程。
二、创建存储过程
1、使用企业管理器
2、使用向导
3、 Transact-SQL语句
CREATE PROC[EDURE] procedure_name
[({ [@parameter data_type}[=default][output]) ] [,…n]
AS
sql_statement
procedure_name:新存储过程的名称。过程名必须符合标识符规则，且对于数据库唯一。
@parameter： 过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明的参数的值（除非定义了该参数的默认值）
data_type：参数的数据类型。
Default：参数的默认值。
Output：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用此参数可将信息返回给调用过程。
AS：指定过程要执行的操作。
sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。
例1：将Employee表中所有员工的工资列Wage增加10％。
CREATE PROCEDURE wageincrease
AS
update Employee
set Wage=Wage*1.1
CREATE PROCEDURE wageincrease
@incrate smallint
AS update Employee
set Wage=Wage+Wage*@ incrate/100
三、修改存储过程
存储过程可以根据用户的要求或者基表定义的改变而改变。修改已经存在的存储过程语法为：
ALTER PROC[EDURE] procedure_name
[({ [@parameter data_type}[=default][output]) ] [,…n]
AS sql_statement
例如，修改存储过程wageincrease，在参数中增加@dep_id,对指定的部门按照指定的比例上调工资。
CREATE PROCEDURE wageincrease
@incrate smallint，@Dep_id smallint
AS update Employee
set Wage=Wage+Wage*@ incrate/100
WHERE Dep_id= @Dep_id
四、执行存储过程
EXEC[UTE]
{
{procedure_name}
[ [@parameter =]{value|@variable [output]| [default]} [,…n]
例如，执行存储过程wageincrease：
EXEC wageincrease
@incrate=20
或： EXEC wageincrease 20
五、删除存储过程
DROP PROCEDURE procedure_name
例： DROP PROCEDURE Wageincrease
九、存储过程应用举例
例1：建立一个不带参数的存储过程publisher_proc1,它显示每个出版社所出版的图书及其类别：
CREATE PROCEDURE publisher_proc1
AS
select pub_name, type,title
from titles, publishers
WHERE titles.pub_id= publishers. pub_id
ORDER BY pub_name, type
在实际应用中，常常需要和用户交互，让用户有选择的进行选择，如输入某种类型，查询此种类型的图书。这就需要创建带参数的存储过程：
CREATE PROCEDURE publisher_proc2
(@type char(12))
AS
select pub_name, type,title
from titles, publishers
WHERE titles.pub_id= publishers. pub_id
AND type LIKE @type
exec publisher_proc2 'business‘
或：
declare @t char(12)
set @t='business'
exec publisher_proc2 @t
使用集函数的存储过程：
例2：建立一个存储过程price_proc,其参数具有默认值，它返回用户指定类图书的数量及其平均价格，默认时为“business”类图书：
CREATE PROCEDURE price_proc
(@count int OUTPUT, @avg_price money OUTPUT,
@type char(12)=‘business’)
AS
select @count=count(*), @avg_price=avg(price)
from titles
WHERE type= @type
declare @quantity int
declare @average_price money
declare @buf char(128)
EXECUTE price_proc @quantity OUTPUT, @average_price OUTPUT
Select @buf=‘指定类图书共有’＋str(@quantity,2 )＋
’种，平均价格为‘＋str(@average_price,6,2 )
print @buf
注意：执行存储过程中需要使用变量时，必须先声明。

展开更多......

收起↑