資源簡介

(共27張PPT)
WELCOME TO 數(shù)據(jù)庫
商貿(mào)系
齊莉麗
E-mail: qiliru@
第七節(jié) 觸發(fā)器
一、觸發(fā)器的基本概念
觸發(fā)器與普通存儲過程的不同之處在于：觸發(fā)器的執(zhí)行是由事件觸發(fā)的，而普通存儲過程是由命令調(diào)用的。
觸發(fā)器是一種特殊類型的存儲過程，它在指定表中的數(shù)據(jù)發(fā)生變化時自動生效。
觸發(fā)器可以實施更為復(fù)雜的數(shù)據(jù)完整性約束。使用觸發(fā)器的目的是為了更好的維護(hù)企業(yè)的業(yè)務(wù)規(guī)則。
觸發(fā)器主要提供一下功能：
1、觸發(fā)器是自動的：它們在對表的數(shù)據(jù)進(jìn)行了任何修改之后立即被激活。
2、撤銷或回滾違反引用完整性的操作，防止非法修改數(shù)據(jù)。
3、執(zhí)行比檢查約束（CHECK）更復(fù)雜的約束操作。
4、級聯(lián)修改數(shù)據(jù)庫中的所有相關(guān)表。
5、在一張表的同一類型的操作（插入、更新或刪除）上設(shè)置多個觸發(fā)器，從而可以針對同樣的修改語句執(zhí)行不同的多種操作。
SQL Server系統(tǒng)提供了兩種觸發(fā)器選項：
INSTEAD OF觸發(fā)器。執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而替代觸發(fā)語句的操作。
AFTER觸發(fā)器。在執(zhí)行了INSERT、UPDATE、DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。
INSTEAD OF觸發(fā)器和AFTER觸發(fā)器的功能比較
功能
INSTEAD OF觸發(fā)器
AFTER觸發(fā)器
適用范圍
表和視圖
表
每個表或視圖含觸發(fā)器數(shù)量
每個觸發(fā)動作（UPDATE、DELETE和INSERT）含一個觸發(fā)器
每個觸發(fā)動作（UPDATE、DELETE和INSERT）含多個觸發(fā)器
執(zhí)行
早于：
約束處理
代替：
觸發(fā)動作
晚于：
inserted和deleted表的創(chuàng)建
晚于：
約束處理
聲明引用操作
inserted和deleted表的創(chuàng)建
觸發(fā)動作
二、inserted和deleted表
觸發(fā)器語句中使用了兩種特殊的表。SQL Server系統(tǒng)自動創(chuàng)建和管理這些表?？梢允褂眠@兩個表測試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)的條件，但不能直接對表中的數(shù)據(jù)進(jìn)行修改。
deleted表用于存儲DELETE和UPDATE語句所影響的行的復(fù)本。在執(zhí)行DELETE和UPDATE語句時，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。 deleted表和觸發(fā)器表通常沒有相同的行。
inserted表用于存儲DELETE和UPDATE語句所影響的行的復(fù)本。在一個插入或更新事務(wù)處理中，新建行被同時添加到inserted表和觸發(fā)器表中。 inserted表中的行是觸發(fā)器表中新行的副本。
更新事務(wù)類似于在刪除之后執(zhí)行插入；首先舊行被復(fù)制到deleted表中，然后新行被復(fù)制到觸發(fā)器表和inserted表中。
三、創(chuàng)建觸發(fā)器
CREATE TRIGGER trigger_name
ON table\view
{ {FOR| AFTER| INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]}
AS
sql_statement[…n]
}
trigger_name:觸發(fā)器的名字。命名必須遵守SQL Server的數(shù)據(jù)庫對象命名規(guī)則。
ON table\view：在哪一個表或視圖上創(chuàng)建觸發(fā)器。觸發(fā)器雖然可引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。
AFTER：默認(rèn)的觸發(fā)器類型，可以不注明。
AS：是觸發(fā)器要執(zhí)行的操作。
sql_statement：是觸發(fā)器的條件和操作。
四、刪除觸發(fā)器
DROP TRIGGER trigger_name
例： DROP TRIGGER tr_product_update
五、修改觸發(fā)器
ALTER TRIGGER trigger_name
六、觸發(fā)器綜合應(yīng)用舉例
例1：要在表Department中定義一個觸發(fā)器DepIDChange,當(dāng)表Department中的列Dep_ID被更新時，將表workers 中相應(yīng)的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
第八節(jié) 存儲過程
一、存儲過程的基本概念
存儲過程是Transact-SQL語句的預(yù)編譯集合，這些語句在一個名稱下存儲并作為一個單元進(jìn)行處理。存儲過程可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。
使用存儲過程能夠改變Transact-SQL語句的運行性能，提高其執(zhí)行效率。
在SQL Server系統(tǒng)中，對于某些比較固定的任務(wù)，可以將完成該任務(wù)的各種命令集中起來，寫成一個新命令，執(zhí)行該命令就可以完成該項任務(wù)。這種方法稱為存儲過程。
二、創(chuàng)建存儲過程
1、使用企業(yè)管理器
2、使用向?qū)?br/>3、 Transact-SQL語句
CREATE PROC[EDURE] procedure_name
[({ [@parameter data_type}[=default][output]) ] [,…n]
AS
sql_statement
procedure_name:新存儲過程的名稱。過程名必須符合標(biāo)識符規(guī)則，且對于數(shù)據(jù)庫唯一。
@parameter： 過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明的參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）
data_type：參數(shù)的數(shù)據(jù)類型。
Default：參數(shù)的默認(rèn)值。
Output：表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXEC[UTE]。使用此參數(shù)可將信息返回給調(diào)用過程。
AS：指定過程要執(zhí)行的操作。
sql_statement：過程中要包含的任意數(shù)目和類型的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
三、修改存儲過程
存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。修改已經(jīng)存在的存儲過程語法為：
ALTER PROC[EDURE] procedure_name
[({ [@parameter data_type}[=default][output]) ] [,…n]
AS sql_statement
例如，修改存儲過程wageincrease，在參數(shù)中增加@dep_id,對指定的部門按照指定的比例上調(diào)工資。
CREATE PROCEDURE wageincrease
@incrate smallint，@Dep_id smallint
AS update Employee
set Wage=Wage+Wage*@ incrate/100
WHERE Dep_id= @Dep_id
四、執(zhí)行存儲過程
EXEC[UTE]
{
{procedure_name}
[ [@parameter =]{value|@variable [output]| [default]} [,…n]
例如，執(zhí)行存儲過程wageincrease：
EXEC wageincrease
@incrate=20
或： EXEC wageincrease 20
五、刪除存儲過程
DROP PROCEDURE procedure_name
例： DROP PROCEDURE Wageincrease
九、存儲過程應(yīng)用舉例
例1：建立一個不帶參數(shù)的存儲過程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
在實際應(yīng)用中，常常需要和用戶交互，讓用戶有選擇的進(jìn)行選擇，如輸入某種類型，查詢此種類型的圖書。這就需要創(chuàng)建帶參數(shù)的存儲過程：
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
使用集函數(shù)的存儲過程：
例2：建立一個存儲過程price_proc,其參數(shù)具有默認(rèn)值，它返回用戶指定類圖書的數(shù)量及其平均價格，默認(rèn)時為“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
注意：執(zhí)行存儲過程中需要使用變量時，必須先聲明。

展開更多......

收起↑