觸發(fā)器原理：

觸發(fā)器與存儲過程非常相似，觸發(fā)器也是SQL語句集，兩者唯一的區(qū)別是觸發(fā)器不能用EXECUTE語句調(diào)用，而是在用戶執(zhí)行Transact-SQL語句時(shí)自動觸發(fā)（激活）執(zhí)行。觸發(fā)器是在一個(gè)修改了指定表中的數(shù)據(jù)時(shí)執(zhí)行的存儲過程。通常通過創(chuàng)建觸發(fā)器來強(qiáng)制實(shí)現(xiàn)不同表中的邏輯相關(guān)數(shù)據(jù)的引用完整性和一致性。由于用戶不能繞過觸發(fā)器，所以可以用它來強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)則，以確保數(shù)據(jù)的完整性。觸發(fā)器不同于存儲過程，觸發(fā)器主要是通過事件執(zhí)行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱名字而直接調(diào)用。當(dāng)對某一表進(jìn)行諸如UPDATE、INSERT、DELETE這些操作時(shí)，SQLSERVER就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合這些SQL語句所定義的規(guī)則。

觸發(fā)器的作用：

觸發(fā)器的主要作用是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。它能夠?qū)?shù)據(jù)庫中的相關(guān)表進(jìn)行級聯(lián)修改，強(qiáng)制比CHECK約束更復(fù)雜的數(shù)據(jù)完整性，并自定義操作消息，維護(hù)非規(guī)范化數(shù)據(jù)以及比較數(shù)據(jù)修改前后的狀態(tài)。與CHECK約束不同，觸發(fā)器可以引用其它表中的列。在下列情況下使用觸發(fā)器實(shí)現(xiàn)復(fù)雜的引用完整性；強(qiáng)制數(shù)據(jù)間的完整性。創(chuàng)建多行觸發(fā)器，當(dāng)插入，更新、刪除多行數(shù)據(jù)時(shí)，必須編寫一個(gè)處理多行數(shù)據(jù)的觸發(fā)器。執(zhí)行級聯(lián)更新或級聯(lián)刪除這樣的動作。級聯(lián)修改數(shù)據(jù)庫中所有相關(guān)表。撤銷或者回滾違反引用完整性的操作，防止非法修改數(shù)據(jù)。

觸發(fā)器與存儲過程的區(qū)別：

觸發(fā)器與存儲過程的主要區(qū)別在于觸發(fā)器的運(yùn)行方式。存儲過程必須有用戶、應(yīng)用程序或者觸發(fā)器來顯示的調(diào)用并執(zhí)行，而觸發(fā)器是當(dāng)特定時(shí)間出現(xiàn)的時(shí)候，自動執(zhí)行或者激活的，與連接用數(shù)據(jù)庫中的用戶、或者應(yīng)用程序無關(guān)。當(dāng)一行被插入、更新或者刪除時(shí)觸發(fā)器才執(zhí)行，同時(shí)還取決于觸發(fā)器是怎樣創(chuàng)建的，當(dāng)UPDATE發(fā)生時(shí)使用一個(gè)更新觸發(fā)器，當(dāng)INSERT發(fā)生時(shí)使用一個(gè)插入觸發(fā)器，當(dāng)DELETE發(fā)生時(shí)使用一個(gè)刪除觸發(fā)器。

§1 觸發(fā)器類型

§1.1 DML觸發(fā)器

Oracle可以在DML語句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)，并且可以對每個(gè)行或語句操作上進(jìn)行觸發(fā)。

§1.2替代觸發(fā)器

由于在Oracle里，不能直接對由兩個(gè)以上的表建立的視圖進(jìn)行操作。所以給出了替代觸發(fā)器。它就是Oracle8專門為進(jìn)行視圖操作的一種處理方法。

§1.3系統(tǒng)觸發(fā)器

Oracle8i 提供了第三種類型的觸發(fā)器叫系統(tǒng)觸發(fā)器。它可以在Oracle數(shù)據(jù)庫系統(tǒng)的事件中進(jìn)行觸發(fā)，如Oracle系統(tǒng)的啟動與關(guān)閉等。

§2 創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的一般語法是：

CREATE ［ OR REPLACE］TRIGGER trigger_name

［ BEFORE|AFTER ］trigger_event ON table_reference

［ FOR EACH ROW ［WHEN trigger_condition］ ］

trigger_body;

當(dāng)一個(gè)基表被修改（ insert，update，delete）時(shí)要執(zhí)行的內(nèi)嵌過程。執(zhí)行時(shí)根據(jù)其所依附的 基表改動而自動觸發(fā)，因此與應(yīng)用程序無關(guān)，用數(shù)據(jù)庫觸發(fā)器可以保證數(shù)據(jù)的一致性和完整性。

每張表最多可建立 12 個(gè)觸發(fā)器，它們是：

before insert

before insert for each row

after insert

after insert for each row

before update

before update for each row

after update

after update for each row

before delete

before delete for each row

after delete

after delete for each row

§3 創(chuàng)建DML觸發(fā)器

觸發(fā)器名與過程名和包的名字不一樣，它是單獨(dú)的名字空間，因而觸發(fā)器名可以和 表 或過程 有相同的名字，但在一個(gè)模式中觸發(fā)器名不能相同。

觸發(fā)器的限制

觸發(fā)器有下面一些限制：

。觸發(fā)器中不能使用控制語句 COMMIT，ROLLBACK， SVAEPOINT 語句；

。由觸發(fā)器所調(diào)用的過程或函數(shù)也不能使用控制語句；

。觸發(fā)器中不能使用LONG，LONG RAW 類型；

。觸發(fā)器所訪問的表受到遠(yuǎn)表的約束限制，即后面的“變化表”。

問題：當(dāng)觸發(fā)器被觸發(fā)時(shí)，要使用被插入，更新或刪除的記錄中的列值，有時(shí)要使用操作前，

后列的值。

實(shí)現(xiàn)： ：new 修飾符訪問操作完成后列的值

：old 修飾符訪問操作完成前列的值

例1： 建立一個(gè)觸發(fā)器，當(dāng)職工表 emp 表被刪除一條記錄時(shí)，把被刪除記錄寫到職工表刪除日志表中去。

create or replace trigger scott.del_emp

before delete on scott.emp for each row

begin

-- 將 修改前數(shù)據(jù)插入到 日志記錄 表 del_emp， 以供監(jiān)督使用。

insert into emp_his（ deptno ， empno， ename ， job ，mgr ， sal ， comm ， hiredate ）

values（ ：old.deptno， ：old.empno， ：old.ename ， ：old.job，

：old.mgr， ：old.sal， ：old.comm， ：old.hiredate ）;

end;

/

show errors

§4 創(chuàng)建替代（Instead_of）觸發(fā)器

Instead_of 用于對視圖的DML觸發(fā)，由于視圖有可能是由多個(gè)表進(jìn)行聯(lián)結(jié)（join）而成，因而并非是所有的聯(lián)結(jié)都是可更新的。但可以按照所需的方式執(zhí)行更新，例如下面情況：

--節(jié)選自在線代碼 instead.sql

CREATE VIEW room_summary AS

SELECT building，sum（number_seats） total_seats

FROM rooms GROUP BY building;

在此視圖中直接刪除是非法的：

SQL》DELETE FROM rooms_summary WHERE building=’Building 7’;

DELETE FROM rooms_summary WHERE building=’Building 7’;

*

ERROR at line 1：

ORA-01732：data manipulation operation not legal on this view

但是我們可以創(chuàng)建Instead_of 觸發(fā)器來為 DELETE 操作執(zhí)行所需的處理，即刪除rooms 表中所有基準(zhǔn)行：

--節(jié)選自在線代碼 instead.sql

CREATE TRIGGER room_summary_delete

INSTEAD OF DELETE ON room_summary

FOR EACH ROW

BEGIN

-- 刪除表 room 中行，這些行構(gòu)成單個(gè)視圖行。

DELETE FROM rooms WHERE building = ：old.building;

END room_summary_delete;

§5 創(chuàng)建系統(tǒng)觸發(fā)器

Oracle8i提供的系統(tǒng)觸發(fā)器可以在DDL或數(shù)據(jù)庫系統(tǒng)上被觸發(fā)。DDL指的是數(shù)據(jù)定義語言，如CREATE ，ALTER及DROP 等。而數(shù)據(jù)庫系統(tǒng)事件包括數(shù)據(jù)庫服務(wù)器的啟動或關(guān)閉，用戶的登錄與退出、數(shù)據(jù)庫服務(wù)錯(cuò)誤等。創(chuàng)建系統(tǒng)觸發(fā)器的語法如下：

CREATE OR REPLACE TRIGGER ［sachema.］ trigger_name

{BEFORE|AFTER}

{ddl_event_list|database_event_list}

ON { DATABASE | ［schema.］ SCHEMA }

［ when_clause］ trigger_body;

ddl_event_list： 一個(gè)或多個(gè)DDL 事件，事件間用 OR 分開；

database_event_list： 一個(gè)或多個(gè)數(shù)據(jù)庫事件，事件間用 OR 分開；

下面給出系統(tǒng)觸發(fā)器的種類和事件出現(xiàn)的時(shí)機(jī)（前或后）：

系統(tǒng)觸發(fā)器可以在數(shù)據(jù)庫級（database）或模式（schema）級進(jìn)行定義。數(shù)據(jù)庫級觸發(fā)器在任何事件都激活觸發(fā)器，而模式觸發(fā)器只有在指定的模式的觸發(fā)事件發(fā)生時(shí)才觸發(fā)。

例：建立一個(gè)當(dāng)用戶USERA登錄時(shí)，自動記錄一些信息的觸發(fā)器：

CREATE OR REPLACE TRIGGER loguserAconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES（1，’LogUserAConnects fired！’）;

END loguserAconnects;

例：建立一個(gè)當(dāng)用戶USERB登錄時(shí)，自動記錄一些信息的觸發(fā)器：

CREATE OR REPLACE TRIGGER loguserAconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES（2，’LogUserAConnects fired！’）;

END loguserBconnects;

例：建立一個(gè)當(dāng)所有用戶登錄時(shí)，自動記錄一些信息的觸發(fā)器：

CREATE OR REPLACE TRIGGER logALLconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES（3，’LogUserAConnects fired！’）;

END logALLconnects;

SQL》connect usera/usera

Connected.

SQL》connect userb/userb

Connected.

SQL》connect scott/tiger

Connected.

SQL》select * from temp_table;

Num_COL CHAR_COL

-------------- --------------------------------

3 LogALLConnects fired！

2 LoguserBConnects fired！

3 LogALLConnects fired！

3 LogALLConnects fired！

1 LoguserAConnects fired！

§6 觸發(fā)器觸發(fā)次序

Oracle 對事件的觸發(fā)共有16種，但是它們的觸發(fā)是有次序的，基本觸發(fā)次序如下：

1） 執(zhí)行 BEFORE語句級觸發(fā)器;

2） 對與受語句影響的每一行：

a） 執(zhí)行 BEFORE語句行級觸發(fā)器

b） 執(zhí)行 DML語句

c） 執(zhí)行 AFTER行級觸發(fā)器

3）執(zhí)行 AFTER語句級觸發(fā)器

§7 使用觸發(fā)器謂詞

ORACLE 提供三個(gè)參數(shù) INSERTING，UPDATEING，DELETING 用于判斷觸發(fā)了哪些操作。謂詞的行為如下：

例

--節(jié)選自在線代碼 Rschange.sql

REM 選自：RSchange.sql

REM 作者： Scott Urman.

REM 中文注釋：趙元杰

CREATE OR REPLACE TRIGGER LogRSChanges

BEFORE INSERT OR DELETE OR UPDATE ON registered_students

FOR EACH ROW

DECLARE

v_ChangeType CHAR（1）;

BEGIN

/* INSERT 用’I’， DELETE用’D’， UPDATE 用’U’ */

IF INSERTING THEN

v_ChangeType ：= ‘I’;

ELSIF UPDATING THEN

v_ChangeType ：= ‘U’;

ELSE

v_ChangeType ：= ‘D’;

END IF;

/* 在RS_audit 記錄所有的改變，使用sysdate 來產(chǎn)生系統(tǒng)時(shí)間郵戳，

使用 user 返回當(dāng)前用戶的標(biāo)識 */

INSERT INTO RS_audit

（change_type， changed_by， timestamp，

old_student_id， old_department， old_course， old_grade，

new_student_id， new_department， new_course， new_grade）

VALUES

（v_ChangeType， USER， SYSDATE，

：old.student_id， ：old.department， ：old.course， ：old.grade，

：new.student_id， ：new.department， ：new.course， ：new.grade）;

END LogRSChanges;

/

§8 刪除和使能觸發(fā)器

當(dāng)觸發(fā)器創(chuàng)建完成后，程序員和DBA管理員要經(jīng)常關(guān)心數(shù)據(jù)庫實(shí)例中的觸發(fā)器的情況。對于不必需的觸發(fā)器，要進(jìn)行刪除或使觸發(fā)器無效，從而使系統(tǒng)的性能有所提高。

刪除觸發(fā)器的命令語法如下：

DROP TRIGGER trigger_name;

例：從數(shù)據(jù)子字典中刪除某個(gè)觸發(fā)器：

SQL》 select trigger_name from user_triggers;

TRIGGER_NAME

------------------------------

SET_NLS

SQL》 drop trigger set_nls;

觸發(fā)器已丟棄

使觸發(fā)器無效的命令是ALTER TRIGGER，它的語法如下：

ALTER TRIGGER triiger_name ［DISABLE | ENABLE ］;

如：

SQL》 ALTER TRIGGER updatemajorstats DISABLE;

SQL》 alter table students disable all triggers;

§9 創(chuàng)建觸發(fā)器的限制

編寫觸發(fā)器程序時(shí)有些限制，希望程序人員注意下面的一些情況：

1.代碼大小：

一般的觸發(fā)器的代碼大小必須小于32K；如果大于這個(gè)限制，可以將其拆成幾個(gè)部分來寫。

2.觸發(fā)器中有效的語句：

可以包括DML SQL語句，但不能包括DDL 語句。ROLLBACK， COMMIT， and SAVEPOINT也不能使用。但是，對于“系統(tǒng)觸發(fā)器（system triggers）”可以使用CREATE/ALTER/DROP TABLE和Alter … COMPILE語句。

3. LONG， LONG RAW和LOB的限制：

l 不能插入數(shù)據(jù)到LONG或LONG RAW;

l 來自LONG或LONG RAW的數(shù)據(jù)可以轉(zhuǎn)換成字符型（如CHAR和VARCHAR2），但是只允許32KB；

l 使用LONG或LONG RAW不能聲明變量；

l 在LONG或LONG RAW列中不能用：NEW 和 ：PARENT；

l LOB中的：NEW變量不能修改，例如：

：NEW.Column ：= 。。。

4. 引用包變量的限制：

如果UPDATE或DELETE語句測到與當(dāng)前的UPADTE沖突，則Oracle執(zhí)行ROLLBACK到SAVEPOINT上并重新啟動更新。這樣可以要出現(xiàn)多次才能成功。

觸發(fā)器與存儲過程的編程代碼

觸發(fā)器

現(xiàn)有字典表（Dict）

需求一：當(dāng)新增一條記錄的時(shí)候，若已存在相同鍵的，拒絕插入

//操作步驟：展開相關(guān)表，右擊‘觸發(fā)器’，新建即可

USE ［sqlffwj］

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER ［dbo］。［CheckKeyRepeated］

ON ［dbo］。［Dict］

for INSERT

AS

if（select COUNT（*） from ［Dict］， inserted inobj where ［Dict］.ItemKey = inobj.ItemKey and ［Dict］.Id ！= inobj.Id） 》 0

BEGIN

raiserror（‘已有相同鍵，不能插入’，16，1）

rollback tran

END

需求二：當(dāng)刪除一條記錄的時(shí)候，若有下層記錄，拒絕刪除

USE ［sqlffwj］

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER ［dbo］。［CheckDependence］

ON ［dbo］。［Dict］

for delete

AS

if（select COUNT（*） from ［Dict］， deleted delbj where ［Dict］.UpperId = delbj.Id） 》 0

BEGIN

raiserror（‘有下層記錄，不能刪除’，16，1）

rollback tran

END

需求三：當(dāng)刪除一條記錄的時(shí)候，若有下層記錄，下層也一起刪除

USE ［sqlffwj］

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER ［dbo］。［CheckDependence2］

ON ［dbo］。［Dict］

AFTER delete

AS

while（select COUNT（*） from ［Dict］ where UpperId ！= 0 and UpperId not in （select Id from ［Dict］）） 》 0

BEGIN

delete from ［Dict］

where UpperId ！= 0 and UpperId not in （select Id from ［Dict］）

END

存儲過程

現(xiàn)有用戶表（User）

需求一：用存儲過程查詢所有用戶的信息（標(biāo)示、姓名、年齡、部門名）

//操作步驟：展開數(shù)據(jù)庫，再展開可編程性，右擊‘存儲過程’，新建即可

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE ［dbo］。［GetUsers］

AS

BEGIN

select ［User］.Id ‘標(biāo)示’，［User］.Name ‘姓名’， ［User］.Age ‘年齡’， ［Dept］.Name ‘部門’ from ［User］ left join ［Dept］ on ［User］.DeptId = ［Dept］.Id

END

GO

/* 調(diào)用 */

exec GetUsers

需求二：用存儲過程查詢指定部門的用戶信息（標(biāo)示、姓名、年齡、部門名）

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE ［dbo］。［GetUsersByDept］

（

@deptid int /* 部門標(biāo)示 */

）

AS

BEGIN

select ［User］.Id ‘標(biāo)示’，［User］.Name ‘姓名’， ［User］.Age ‘年齡’， ［Dept］.Name ‘部門’

from ［User］ left join ［Dept］ on ［User］.DeptId = ［Dept］.Id

where ［Dept］.Id = @deptid

END

GO

/* 調(diào)用 */

exec GetUsersByDept 2

需求三：在項(xiàng)目中用ADO調(diào)用存儲過程‘GetUsersByDept’

1、環(huán)境：VS2010+sql2008

2、新建edmx文件，引用兩張表和存儲過程

3、切換到‘模型瀏覽器’，‘添加函數(shù)導(dǎo)入’

4、在‘添加函數(shù)導(dǎo)入’面板，點(diǎn)擊‘獲取列信息’，獲取到列信息后再點(diǎn)擊‘創(chuàng)建新的復(fù)雜類型’，確定后就可以通過Func調(diào)用存儲過程了

5、調(diào)用代碼Demo

using （var context = new SqltestEntities（））

{

var result = context.GetUsersByDept（2）;

throw new Exception（result.Count（）.ToString（））;

}