觸發(fā)器原理：

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

觸發(fā)器的作用：

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

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

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

§1 觸發(fā)器類型

§1.1 DML觸發(fā)器

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

§1.2替代觸發(fā)器

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

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

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

§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;

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

每張表最多可建立 12 個觸發(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ā)器名與過程名和包的名字不一樣，它是單獨的名字空間，因而觸發(fā)器名可以和 表 或過程 有相同的名字，但在一個模式中觸發(fā)器名不能相同。

觸發(fā)器的限制

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

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

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

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

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

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

后列的值。

實現： ：new 修飾符訪問操作完成后列的值

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

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

create or replace trigger scott.del_emp

before delete on scott.emp for each row

begin

-- 將 修改前數據插入到 日志記錄 表 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ā)，由于視圖有可能是由多個表進行聯結（join）而成，因而并非是所有的聯結都是可更新的。但可以按照所需的方式執(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 表中所有基準行：

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

CREATE TRIGGER room_summary_delete

INSTEAD OF DELETE ON room_summary

FOR EACH ROW

BEGIN

-- 刪除表 room 中行，這些行構成單個視圖行。

DELETE FROM rooms WHERE building = ：old.building;

END room_summary_delete;

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

Oracle8i提供的系統(tǒng)觸發(fā)器可以在DDL或數據庫系統(tǒng)上被觸發(fā)。DDL指的是數據定義語言，如CREATE ，ALTER及DROP 等。而數據庫系統(tǒng)事件包括數據庫服務器的啟動或關閉，用戶的登錄與退出、數據庫服務錯誤等。創(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： 一個或多個DDL 事件，事件間用 OR 分開；

database_event_list： 一個或多個數據庫事件，事件間用 OR 分開；

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

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

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

CREATE OR REPLACE TRIGGER loguserAconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES（1，’LogUserAConnects fired！’）;

END loguserAconnects;

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

CREATE OR REPLACE TRIGGER loguserAconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES（2，’LogUserAConnects fired！’）;

END loguserBconnects;

例：建立一個當所有用戶登錄時，自動記錄一些信息的觸發(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 提供三個參數 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 來產生系統(tǒng)時間郵戳，

使用 user 返回當前用戶的標識 */

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ā)器

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

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

DROP TRIGGER trigger_name;

例：從數據子字典中刪除某個觸發(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ā)器程序時有些限制，希望程序人員注意下面的一些情況：

1.代碼大?。?/p>

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

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 不能插入數據到LONG或LONG RAW;

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

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

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

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

：NEW.Column ：= 。。。

4. 引用包變量的限制：

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

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

觸發(fā)器

現有字典表（Dict）

需求一：當新增一條記錄的時候，若已存在相同鍵的，拒絕插入

//操作步驟：展開相關表，右擊‘觸發(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

需求二：當刪除一條記錄的時候，若有下層記錄，拒絕刪除

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

需求三：當刪除一條記錄的時候，若有下層記錄，下層也一起刪除

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

存儲過程

現有用戶表（User）

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

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE ［dbo］。［GetUsers］

AS

BEGIN

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

END

GO

/* 調用 */

exec GetUsers

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE ［dbo］。［GetUsersByDept］

（

@deptid int /* 部門標示 */

）

AS

BEGIN

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

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

where ［Dept］.Id = @deptid

END

GO

/* 調用 */

exec GetUsersByDept 2

需求三：在項目中用ADO調用存儲過程‘GetUsersByDept’

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

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

3、切換到‘模型瀏覽器’，‘添加函數導入’

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

5、調用代碼Demo

using （var context = new SqltestEntities（））

{

var result = context.GetUsersByDept（2）;

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

}