觸發(fā)器一 觸發(fā)器介紹
觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它在插入，刪除或修改特定表中
的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行，它比數(shù)據(jù)庫(kù)本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的
數(shù)據(jù)控制能力。數(shù)據(jù)庫(kù)觸發(fā)器有以下的作用：
* 安全性。可以基于數(shù)據(jù)庫(kù)的值使用戶具有操作數(shù)據(jù)庫(kù)的某種權(quán)利。
# 可以基于時(shí)間限制用戶的操作，例如不允許下班后和節(jié)假日
修改數(shù)據(jù)庫(kù)數(shù)據(jù)。
# 可以基于數(shù)據(jù)庫(kù)中的數(shù)據(jù)限制用戶的操作，例如不允許股票
的價(jià)格的升幅一次超過(guò)10%。
* 審計(jì)。可以跟蹤用戶對(duì)數(shù)據(jù)庫(kù)的操作。
# 審計(jì)用戶操作數(shù)據(jù)庫(kù)的語(yǔ)句。
# 把用戶對(duì)數(shù)據(jù)庫(kù)的更新寫入審計(jì)表。
* 實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則。
# 實(shí)現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則
更為復(fù)雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫(kù)對(duì)
象。例如，觸發(fā)器可回退任何企圖吃進(jìn)超過(guò)自己保證金的期貨。
# 提供可變的缺省值。
* 實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。觸發(fā)器可以對(duì)數(shù)
據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新。例如，在auths表author_code列上的
刪除觸發(fā)器可導(dǎo)致相應(yīng)刪除在其它表中的與之匹配的行。
# 在修改或刪除時(shí)級(jí)聯(lián)修改或刪除其它表中的與之匹配的行。
# 在修改或刪除時(shí)把其它表中的與之匹配的行設(shè)成NULL值。
# 在修改或刪除時(shí)把其它表中的與之匹配的行級(jí)聯(lián)設(shè)成缺省值。
# 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試
圖進(jìn)行數(shù)據(jù)更新的事務(wù)。當(dāng)插入一個(gè)與其主健不匹配的外部鍵
時(shí)，這種觸發(fā)器會(huì)起作用。例如，可以在books.author_code
列上生成一個(gè)插入觸發(fā)器，如果新值與auths.author_code列
中的某值不匹配時(shí)，插入被回退。
* 同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)。
* 自動(dòng)計(jì)算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特
定的處理。例如，如果公司的帳號(hào)上的資金低于5萬(wàn)元?jiǎng)t立即給財(cái)務(wù)人
員發(fā)送警告數(shù)據(jù)。
ORACLE與SYBASE數(shù)據(jù)庫(kù)的觸發(fā)器有一定的區(qū)別，下面將分別講述
這兩種數(shù)據(jù)庫(kù)觸發(fā)器的作用和寫法。
二 ORACLE 觸發(fā)器
ORACLE產(chǎn)生數(shù)據(jù)庫(kù)觸發(fā)器的語(yǔ)法為：
create [or replace] trigger 觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件
on 表名
[for each row]
pl/sql 語(yǔ)句
其中：
觸發(fā)器名：觸發(fā)器對(duì)象的名稱。由于觸發(fā)器是數(shù)據(jù)庫(kù)自動(dòng)執(zhí)行
的，因此該名稱只是一個(gè)名稱，沒(méi)有實(shí)質(zhì)的用途。
觸發(fā)時(shí)間：指明觸發(fā)器何時(shí)執(zhí)行，該值可取：
before---表示在數(shù)據(jù)庫(kù)動(dòng)作之前觸發(fā)器執(zhí)行；
after---表示在數(shù)據(jù)庫(kù)動(dòng)作之后出發(fā)器執(zhí)行。
觸發(fā)事件：指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器：
insert：數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器；
update：數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器；
delete：數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器。
表 名：數(shù)據(jù)庫(kù)觸發(fā)器所在的表。
for each row：對(duì)表的每一行觸發(fā)器執(zhí)行一次。如果沒(méi)有這一
選項(xiàng)，則只對(duì)整個(gè)表執(zhí)行一次。
舉例：下面的觸發(fā)器在更新表auths之前觸發(fā)，目的是不允許在
周末修改表：
create trigger auth_secure
before insert or update or delete //對(duì)整表更新前觸發(fā)
on auths
begin
if(to_char(sysdate,'DY')='SUN'
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');
end if;
end
三 SYBASE數(shù)據(jù)庫(kù)觸發(fā)器
SYBASE數(shù)據(jù)庫(kù)觸發(fā)器的作用與ORACLE非常類似，僅有較小的差異。
SYBASE產(chǎn)生觸發(fā)器的語(yǔ)法為：
CREATE TRIGGER 觸發(fā)器名
ON 表名
FOR INSERT，UPDATE，DELETE
AS
SQL_statement |
FOR INSERT，UPDATE
AS
IF UPDATE(column_name) [AND|OR UPDATE(column_name)]...
SQL_statements
上面FOR子句用來(lái)指定在觸發(fā)器上的哪些數(shù)據(jù)更新命令可激活該
觸發(fā)器。IF UPDATE子句檢查對(duì)指定列的操作類型，在IF UPDATE子句
中可指定多個(gè)列。
與ORACLE不同，對(duì)于每條SQL語(yǔ)句，觸發(fā)器只執(zhí)行一次。觸發(fā)器
在數(shù)據(jù)更新語(yǔ)句完成以后立即執(zhí)行。觸發(fā)器和啟動(dòng)它的語(yǔ)句被當(dāng)作一
個(gè)事務(wù)處理，事務(wù)可以在觸發(fā)器中回退。
下面舉例說(shuō)明SYBASE觸發(fā)器的寫法。
create trigger forinsert_books
on books
for insert
as
if(select count(*) from auths,inserted
where auths.author_code=insert.author_code)!=@@rowcount
begin
rollback transaction
print "books 表中 author_code 列的值在auths 表中不存在。"
end
----------------------------------------------------------------------------------------------------------
存儲(chǔ)過(guò)程一 存儲(chǔ)過(guò)程介紹
存儲(chǔ)過(guò)程是由流控制和SQL語(yǔ)句書寫的過(guò)程，這個(gè)過(guò)程經(jīng)編譯和優(yōu)化
后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，使用時(shí)只要調(diào)用即可。在ORACLE中，若干個(gè)
有聯(lián)系的過(guò)程可以組合在一起構(gòu)成程序包。
使用存儲(chǔ)過(guò)程有以下的優(yōu)點(diǎn)：
* 存儲(chǔ)過(guò)程的能力大大增強(qiáng)了SQL語(yǔ)言的功能和靈活性。存儲(chǔ)過(guò)程可
以用流控制語(yǔ)句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的
運(yùn)算。
* 可保證數(shù)據(jù)的安全性和完整性。
# 通過(guò)存儲(chǔ)過(guò)程可以使沒(méi)有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)
庫(kù)，從而保證數(shù)據(jù)的安全。
# 通過(guò)存儲(chǔ)過(guò)程可以使相關(guān)的動(dòng)作在一起發(fā)生，從而可以維護(hù)數(shù)據(jù)
庫(kù)的完整性。
* 再運(yùn)行存儲(chǔ)過(guò)程前，數(shù)據(jù)庫(kù)已對(duì)其進(jìn)行了語(yǔ)法和句法分析，并給出
了優(yōu)化執(zhí)行方案。這種已經(jīng)編譯好的過(guò)程可極大地改善SQL語(yǔ)句的性能。
由于執(zhí)行SQL語(yǔ)句的大部分工作已經(jīng)完成，所以存儲(chǔ)過(guò)程能以極快的速度執(zhí)
行。
* 可以降低網(wǎng)絡(luò)的通信量。
* 使體現(xiàn)企業(yè)規(guī)則的運(yùn)算程序放入數(shù)據(jù)庫(kù)服務(wù)器中，以便：
# 集中控制。
# 當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)在服務(wù)器中改變存儲(chǔ)過(guò)程即可，無(wú)須修改
任何應(yīng)用程序。企業(yè)規(guī)則的特點(diǎn)是要經(jīng)常變化，如果把體現(xiàn)企業(yè)規(guī)則的運(yùn)
算程序放入應(yīng)用程序中，則當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)，就需要修改應(yīng)用程序
工作量非常之大（修改、發(fā)行和安裝應(yīng)用程序）。如果把體現(xiàn)企業(yè)規(guī)則的
運(yùn)算放入存儲(chǔ)過(guò)程中，則當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)，只要修改存儲(chǔ)過(guò)程就可
以了，應(yīng)用程序無(wú)須任何變化。
不同數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的寫法不一，在后面的講座中將分別介紹ORACLE
和SYBASE存儲(chǔ)過(guò)程的用法。
二 ORACLE 的存儲(chǔ)過(guò)程
ORACLE 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法為：
create [or replace] procedure 過(guò)程名
參數(shù)1 [in|out|in out] 數(shù)據(jù)類型
[,參數(shù)2 [in|out|in out] 數(shù)據(jù)類型]...
{is|as} pl/sql 語(yǔ)句
下面舉例說(shuō)明ORACLE數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的寫法和用法。
可以建立一個(gè)存儲(chǔ)過(guò)程，每當(dāng)用戶修改數(shù)據(jù)庫(kù)的重要數(shù)據(jù)時(shí)，即把
用戶的用戶名、日期和操作類型記錄下來(lái)：
create procedure update_log is
begin
insert into update_log_tab(use_name,update_date,operation)
values(user,sysdate,'update'
end;
可以在恰當(dāng)?shù)奈恢谜{(diào)用這個(gè)存儲(chǔ)過(guò)程來(lái)記錄用戶對(duì)表的修改。例如下面在
表sal_comm上建立一個(gè)修改觸發(fā)器，每當(dāng)用戶修改此表后，用戶的名稱、修改
時(shí)間和操作即被記錄在了表update_log_tab中：
create trigger audit_update
after update on sal_comm
for each row
begin
update_log
end
三 Sybase的存儲(chǔ)過(guò)程
盡管Sybase存儲(chǔ)過(guò)程的功能和寫法與ORACLE類似，但他們之間還是
有一定的差別。下面講述SYBASE的存儲(chǔ)過(guò)程。
SYBASE可以用CREATE PROCedure命令生成存儲(chǔ)過(guò)程：
CREATE PROCedure 存儲(chǔ)過(guò)程名 [；number]
[[(] @parameter_name datatype [=default] [OUTput]
[, @parameter_name datatype [=default] [OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
下面是一個(gè)查詢作者編碼、名稱和生日的存儲(chǔ)過(guò)程：
create proc p_auths @author_code varchar(10)
as
select author_code, name, birthdate
from auths
where author_code=@author_code
下面執(zhí)行過(guò)程p_auths:
p_auths @author_code=A00001
在CREATE PROC語(yǔ)句中，可以為參數(shù)賦缺省值，該值可以是任何常量。
當(dāng)用戶不提供參數(shù)值時(shí)，該值便作為參數(shù)值提供給過(guò)程。
Sybase的存儲(chǔ)過(guò)程是集中存儲(chǔ)在SQL Server中的預(yù)先定義且已經(jīng)編譯好的事務(wù)。存儲(chǔ)過(guò)程由SQL語(yǔ)句和流程控制語(yǔ)句組成。
它的功能包括:接受參數(shù);調(diào)用另一過(guò)程;返回一個(gè)狀態(tài)值給調(diào)用過(guò)程或批處理,指示調(diào)用成功或失敗;返回若干個(gè)參數(shù)值給調(diào)
用過(guò)程或批處理,為調(diào)用者提供動(dòng)態(tài)結(jié)果;在遠(yuǎn)程SQL Server中運(yùn)行等。
存儲(chǔ)過(guò)程的性能特
1.存儲(chǔ)過(guò)程是預(yù)編譯過(guò)的,這就意味著它與普通的SQL語(yǔ)句或批處理的SQL語(yǔ)句不同。當(dāng)首次運(yùn)行一個(gè)存儲(chǔ)過(guò)程時(shí),SQL Server的查詢處理器對(duì)其進(jìn)行分析,在排除了語(yǔ)法錯(cuò)誤之后形成存儲(chǔ)在系統(tǒng)中的可執(zhí)行方案。由于查詢處理的大部分工作已經(jīng)完成,所以存儲(chǔ)過(guò)程執(zhí)行速度很快。
2.存儲(chǔ)過(guò)程和待處理的數(shù)據(jù)都放在同一臺(tái)運(yùn)行SQL Server的計(jì)算機(jī)上,使用存儲(chǔ)過(guò)程查詢當(dāng)?shù)氐臄?shù)據(jù),效率自然很高。
3.存儲(chǔ)過(guò)程一般多由Client端通過(guò)存儲(chǔ)過(guò)程的名字進(jìn)行調(diào)用,即跨網(wǎng)傳送的只是存儲(chǔ)過(guò)程的名字及少量的參數(shù)(如果有的話),而不是構(gòu)成存儲(chǔ)過(guò)程的許多SQL語(yǔ)句,因此可以減少網(wǎng)絡(luò)傳輸量,加快系統(tǒng)響應(yīng)速度。
4.存儲(chǔ)過(guò)程還有著如同C語(yǔ)言子函數(shù)那樣的被調(diào)用和返回值的方便特性。所以,存儲(chǔ)過(guò)程大大增強(qiáng)了SQL語(yǔ)言的功能、效率和靈活性。掌握和應(yīng)用好存儲(chǔ)過(guò)程,對(duì)進(jìn)一步發(fā)揮Sybase數(shù)據(jù)庫(kù)系統(tǒng)的強(qiáng)大功能有著重要的意義。
存儲(chǔ)過(guò)程的語(yǔ)法規(guī)則
建立存儲(chǔ)過(guò)程的語(yǔ)法規(guī)則為:
CREATE PROCedure[owner.]procedurename[;number]
[[(]@parameter_name datatype[=default][OUTput]
[,@parameter_name datatype[=default][OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
使用存儲(chǔ)過(guò)程的語(yǔ)法規(guī)則為:
[EXECute][@return-status=]
[[[server.]database.]owner.]procedurename[;number]
[[@parameter_name=]value|[@parameter_name=]@varialbe[OUT put]
[,[@parameter_name=]value|[@parameter_name=]@variable[OU Tput]...]]
[WITH RECOMPILE]
下面簡(jiǎn)要介紹這兩個(gè)命令的常用選項(xiàng)以及建立和使用存儲(chǔ)過(guò)程的要點(diǎn),關(guān)于選項(xiàng)的更為詳細(xì)的說(shuō)明請(qǐng)參考有關(guān)手冊(cè)。
1.[[[server.]database.]owner.]procedure_name:存儲(chǔ)過(guò)程的名字。
2.@parameter_name datatype[=default][OUTput]:形式參數(shù)(形參)的名稱、類型。dfault是賦予的缺省值(可選),OUTput指定本參數(shù)為輸出參數(shù)(可選)。形參是存儲(chǔ)過(guò)程中的自變量,可以有多個(gè),名字必須以@打頭,最長(zhǎng)30個(gè)字符。
3.SQL_statements:定義存儲(chǔ)過(guò)程功能的SQL語(yǔ)句。
4.@return_status:接受存儲(chǔ)過(guò)程返回狀態(tài)值的變量。
5.[@parameter_name=]value:實(shí)際參數(shù)(實(shí)參),@parameter_name 為實(shí)參的名稱(可選)。如果某個(gè)實(shí)參以@parameter_name=value提供, 那么隨后的實(shí)參也都要采用這一形式提供。
6.[@parameter_name=]@varialbe[OUTput]:將變量@varialbe中的值作為實(shí)參傳遞給形參@parameter_name(可選),如果變量@varialb e是用來(lái)接受返回的參數(shù)值,則選項(xiàng)OUTput不可缺少。
存儲(chǔ)過(guò)程的建立和使用
我們將通過(guò)幾個(gè)例子進(jìn)行介紹。
假設(shè)有一個(gè)用下述語(yǔ)句生成的技能工資表RS_LS_GZ_JiNeng:
create table RS_LS_GZ_JiNeng /*技能工資表*/
(GeRen_id char(4), /*個(gè)人代碼*/
RiQi smalldatetime, /*執(zhí)行日期*/
YuanYin_id char(1) null, /*變動(dòng)原因代碼*/
JinE smallmoney) /*技能工資金額*/
該表存儲(chǔ)著某單位員工多年來(lái)技能工資的歷史檔案。
例1.如果要查詢?nèi)w員工的技能工資變動(dòng)歷史,則可先建立一個(gè)存儲(chǔ)過(guò)程p_RsGz_JiNeng_All:
create procedure p_RsGz_JiNeng_All
as
select *
from RS_LS_GZ_JiNeng
order by GeRenid,RiQi
然后用批處理語(yǔ)句調(diào)用存儲(chǔ)過(guò)程p_RsGz_JiNeng_All進(jìn)行查詢:
execute p_RsGz_JiNeng_All
本例只顯示查詢到的數(shù)據(jù),無(wú)輸入、輸出參量,是最簡(jiǎn)單的一個(gè)存儲(chǔ)過(guò)程。
例2.如果要查詢某人技能工資的變動(dòng)歷史,可建立另一個(gè)存儲(chǔ)過(guò)程p_RsGz_JiNeng:
create procedure p_RsGz_JiNeng
@c_GeRenId char(4)
as
select *from RS_LS_GZ_JiNeng
where GeRen_id=@c_GeRenId
order by RiQi
之后用批處理語(yǔ)句調(diào)用存儲(chǔ)過(guò)程p_Rs_Gz_JiNeng進(jìn)行查詢:
declare @GeRenId char(4)
select @GeRenId="0135" /*設(shè)要查詢員工的個(gè)人代碼為"0135" */
execute p_RsGz_JeNeng @c_GeRenId=@GeRenId
存儲(chǔ)過(guò)程p_RsGz_JiNeng中定義了一個(gè)形參@c_GeRenId,是字符型變量。在調(diào)用該過(guò)程的批處理中,既可以用具體的值也可以用變量作為實(shí)參。用變量作實(shí)參(如本例)時(shí),必須用delare語(yǔ)句加以說(shuō)明。注意,在批處理的調(diào)用過(guò)程語(yǔ)句@c_GeRenId=@GeRenId中的@c_GeRenId是存儲(chǔ)過(guò)程p_RsGz_JiNeng中的形參名,不是批處理中的變量,所以不能將它列入d eclare語(yǔ)句的變量單中。
例3.如果要計(jì)算當(dāng)月工資,就必須從工資歷史中查出員工距離當(dāng)前最近的一次技能工資變動(dòng)的結(jié)果:
create procedure p_RsGz_JiNeng_Slt
(@c_GeRenId char(4),@sm_JinE smallmoney output)
as
select @sm_JinE=JinE
from RS_LS_GZ_JiNeng
where RiQi=(select max(RiQi)
from RS_LS_GZ_JiNeng
where GeRenid=@c-GeRenId)/*找出歷史記錄中距離當(dāng)前最近的日期*/
調(diào)用存儲(chǔ)過(guò)程p_RsGz_JiNeng_Slt進(jìn)行查詢:
declare @GeRenId char(4),@JinE smallmoney
select @GeRenid="0135"/*設(shè)要查詢員工的個(gè)人代碼為"0135"*/
select @JinE=0
execute p_RsGz_JiNeng_slt @c_GeRenId=@GeRenId,@sm_JinE=@ JinE output
這里,變量@JinE用來(lái)存儲(chǔ)過(guò)程形參@sm_JinE傳回的金額。在調(diào)用過(guò)程語(yǔ)句中,@sm_JiE = @JinE output中的output不可省略。否則, 變量@JinE將得不到形參傳回的數(shù)值而始終為零(等于初值)。
例4.查到了個(gè)人代碼為"0135"員工的技能工資就顯示其歷史紀(jì)錄,查不到則顯示一條出錯(cuò)信息。
create procedure p_RsGz_JiNeng_Rtn
@c_GeRenId char(4)
as
declare @ErrCode smallint
select @ErrCode=0
if exists(select* from RS-LS-GZ-JiNeng
where GeRenid=@c-GeRenId)
begin
select *
from RS_LS_GZ_JiNeng
whrer GeRen_id=@c_GeRenId
order by RiQi
return @ErrCode
end
esle
begin
select @ErrCode=1
return @ErrCode
end
調(diào)用存儲(chǔ)過(guò)程p_RsGz_JiNeng_Rtn:
declare @GeRenId char(4),@RtnCode smallint
select @GeRenId="0135"
select @RtnCode=0
execute @RtnCode=p_RsGz_JiNeng_Rtn @c_GeRenId=@GeRenId
if @RtnCode=1
print"No this one!"
存儲(chǔ)過(guò)程p_RsGz_JiNeng_Rtn向調(diào)用者返回一個(gè)存儲(chǔ)在變量@ErrC ode里的值,這個(gè)值被稱為狀態(tài)值,它向調(diào)用者反映存儲(chǔ)過(guò)程執(zhí)行的成敗狀態(tài)。在本例中,如果查不到指定員工技能工資的任何記錄時(shí),就認(rèn)為"查無(wú)此人",返回出錯(cuò)狀態(tài)值1。否則,返回成功狀態(tài)值0。
調(diào)用過(guò)程的批處理語(yǔ)句使用變量@RtnCode存儲(chǔ)返回的狀態(tài)值,一旦檢出存儲(chǔ)過(guò)程p_RsG_ JiNeng_Rtn返回了錯(cuò)誤標(biāo)志(@RtnCode=1),就顯示一條信息"No this one!"。
小結(jié)
上述四個(gè)例子簡(jiǎn)要介紹了存儲(chǔ)過(guò)程常用的幾種形式,從中我們已經(jīng)可以領(lǐng)略到它的編程特色以及使用上的靈活性和方便性。
雖然上述例子在調(diào)用存儲(chǔ)過(guò)程時(shí)都是用SQL的批處理語(yǔ)句實(shí)現(xiàn)的, 但并不意味著這是唯一的方法。例如在存儲(chǔ)過(guò)程中調(diào)用存儲(chǔ)過(guò)程(即所謂過(guò)程嵌套)的現(xiàn)象就很常見。另外,在其它Sybase數(shù)據(jù)庫(kù)開發(fā)系統(tǒng)(如PowerBuilder)的script語(yǔ)句中調(diào)用Sybase的存儲(chǔ)過(guò)程也非常普遍。