電子發燒友App
觸發器原理：
觸發器與存儲過程非常相似，觸發器也是SQL語句集，兩者唯一的區別是觸發器不能用EXECUTE語句調用，而是在用戶執行Transact-SQL語句時自動觸發（激活）執行。觸發器是在一個修改了指定表中的數據時執行的存儲過程。通常通過創建觸發器來強制實現不同表中的邏輯相關數據的引用完整性和一致性。由于用戶不能繞過觸發器，所以可以用它來強制實施復雜的業務規則，以確保數據的完整性。觸發器不同于存儲過程，觸發器主要是通過事件執行觸發而被執行的，而存儲過程可以通過存儲過程名稱名字而直接調用。當對某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQLSERVER就會自動執行觸發器所定義的SQL語句，從而確保對數據的處理必須符合這些SQL語句所定義的規則。
觸發器的作用：
觸發器的主要作用是其能夠實現由主鍵和外鍵所不能保證的復雜的參照完整性和數據的一致性。它能夠對數據庫中的相關表進行級聯修改，強制比CHECK約束更復雜的數據完整性，并自定義操作消息，維護非規范化數據以及比較數據修改前后的狀態。與CHECK約束不同，觸發器可以引用其它表中的列。在下列情況下使用觸發器實現復雜的引用完整性；強制數據間的完整性。創建多行觸發器，當插入，更新、刪除多行數據時，必須編寫一個處理多行數據的觸發器。執行級聯更新或級聯刪除這樣的動作。級聯修改數據庫中所有相關表。撤銷或者回滾違反引用完整性的操作，防止非法修改數據。
觸發器與存儲過程的區別：
觸發器與存儲過程的主要區別在于觸發器的運行方式。存儲過程必須有用戶、應用程序或者觸發器來顯示的調用并執行，而觸發器是當特定時間出現的時候，自動執行或者激活的，與連接用數據庫中的用戶、或者應用程序無關。當一行被插入、更新或者刪除時觸發器才執行，同時還取決于觸發器是怎樣創建的，當UPDATE發生時使用一個更新觸發器，當INSERT發生時使用一個插入觸發器，當DELETE發生時使用一個刪除觸發器。
§1 觸發器類型
§1.1 DML觸發器
Oracle可以在DML語句進行觸發，可以在DML操作前或操作后進行觸發，并且可以對每個行或語句操作上進行觸發。
§1.2替代觸發器
由于在Oracle里，不能直接對由兩個以上的表建立的視圖進行操作。所以給出了替代觸發器。它就是Oracle8專門為進行視圖操作的一種處理方法。
§1.3系統觸發器
Oracle8i 提供了第三種類型的觸發器叫系統觸發器。它可以在Oracle數據庫系統的事件中進行觸發，如Oracle系統的啟動與關閉等。
§2 創建觸發器
創建觸發器的一般語法是：
CREATE ［ OR REPLACE］TRIGGER trigger_name
［ BEFORE|AFTER ］trigger_event ON table_reference
［ FOR EACH ROW ［WHEN trigger_condition］ ］
trigger_body;
當一個基表被修改（ insert，update，delete）時要執行的內嵌過程。執行時根據其所依附的 基表改動而自動觸發，因此與應用程序無關，用數據庫觸發器可以保證數據的一致性和完整性。
每張表最多可建立 12 個觸發器，它們是：
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 創建DML觸發器
觸發器名與過程名和包的名字不一樣，它是單獨的名字空間，因而觸發器名可以和 表 或過程 有相同的名字，但在一個模式中觸發器名不能相同。
觸發器的限制
觸發器有下面一些限制：
。觸發器中不能使用控制語句 COMMIT，ROLLBACK， SVAEPOINT 語句；
。由觸發器所調用的過程或函數也不能使用控制語句；
。觸發器中不能使用LONG，LONG RAW 類型；
。觸發器所訪問的表受到遠表的約束限制，即后面的“變化表”。
問題：當觸發器被觸發時，要使用被插入，更新或刪除的記錄中的列值，有時要使用操作前，
后列的值。
實現： ：new 修飾符訪問操作完成后列的值
：old 修飾符訪問操作完成前列的值
例1： 建立一個觸發器，當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。
create or replace trigger scott.del_emp
before delete on scott.emp for each row
begin
-- 將 修改前數據插入到 日志記錄 表 del_emp， 以供監督使用。
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 創建替代（Instead_of）觸發器
Instead_of 用于對視圖的DML觸發，由于視圖有可能是由多個表進行聯結（join）而成，因而并非是所有的聯結都是可更新的。但可以按照所需的方式執行更新，例如下面情況：
--節選自在線代碼 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
但是我們可以創建Instead_of 觸發器來為 DELETE 操作執行所需的處理，即刪除rooms 表中所有基準行：
--節選自在線代碼 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 創建系統觸發器
Oracle8i提供的系統觸發器可以在DDL或數據庫系統上被觸發。DDL指的是數據定義語言，如CREATE ，ALTER及DROP 等。而數據庫系統事件包括數據庫服務器的啟動或關閉，用戶的登錄與退出、數據庫服務錯誤等。創建系統觸發器的語法如下：
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 分開；
下面給出系統觸發器的種類和事件出現的時機（前或后）：
系統觸發器可以在數據庫級（database）或模式（schema）級進行定義。數據庫級觸發器在任何事件都激活觸發器，而模式觸發器只有在指定的模式的觸發事件發生時才觸發。
例：建立一個當用戶USERA登錄時，自動記錄一些信息的觸發器：
CREATE OR REPLACE TRIGGER loguserAconnects
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO example.temp_table
VALUES（1，’LogUserAConnects fired！’）;
END loguserAconnects;
例：建立一個當用戶USERB登錄時，自動記錄一些信息的觸發器：
CREATE OR REPLACE TRIGGER loguserAconnects
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO example.temp_table
VALUES（2，’LogUserAConnects fired！’）;
END loguserBconnects;
例：建立一個當所有用戶登錄時，自動記錄一些信息的觸發器：
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 觸發器觸發次序
Oracle 對事件的觸發共有16種，但是它們的觸發是有次序的，基本觸發次序如下：
1） 執行 BEFORE語句級觸發器;
2） 對與受語句影響的每一行：
a） 執行 BEFORE語句行級觸發器
b） 執行 DML語句
c） 執行 AFTER行級觸發器
3）執行 AFTER語句級觸發器
§7 使用觸發器謂詞
ORACLE 提供三個參數 INSERTING，UPDATEING，DELETING 用于判斷觸發了哪些操作。謂詞的行為如下：
例
--節選自在線代碼 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 來產生系統時間郵戳，
使用 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 刪除和使能觸發器
當觸發器創建完成后，程序員和DBA管理員要經常關心數據庫實例中的觸發器的情況。對于不必需的觸發器，要進行刪除或使觸發器無效，從而使系統的性能有所提高。
刪除觸發器的命令語法如下：
DROP TRIGGER trigger_name;
例：從數據子字典中刪除某個觸發器：
SQL》 select trigger_name from user_triggers;
TRIGGER_NAME
------------------------------
SET_NLS
SQL》 drop trigger set_nls;
觸發器已丟棄
使觸發器無效的命令是ALTER TRIGGER，它的語法如下：
ALTER TRIGGER triiger_name ［DISABLE | ENABLE ］;
如：
SQL》 ALTER TRIGGER updatemajorstats DISABLE;
SQL》 alter table students disable all triggers;
§9 創建觸發器的限制
編寫觸發器程序時有些限制，希望程序人員注意下面的一些情況：
1.代碼大小：
一般的觸發器的代碼大小必須小于32K；如果大于這個限制，可以將其拆成幾個部分來寫。
2.觸發器中有效的語句：
可以包括DML SQL語句，但不能包括DDL 語句。ROLLBACK， COMMIT， and SAVEPOINT也不能使用。但是，對于“系統觸發器（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執行ROLLBACK到SAVEPOINT上并重新啟動更新。這樣可以要出現多次才能成功。
觸發器與存儲過程的編程代碼
觸發器
現有字典表（Dict）
需求一：當新增一條記錄的時候，若已存在相同鍵的，拒絕插入
//操作步驟：展開相關表，右擊‘觸發器’，新建即可
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、環境：VS2010+sql2008
2、新建edmx文件，引用兩張表和存儲過程
3、切換到‘模型瀏覽器’，‘添加函數導入’
4、在‘添加函數導入’面板，點擊‘獲取列信息’，獲取到列信息后再點擊‘創建新的復雜類型’，確定后就可以通過Func調用存儲過程了
5、調用代碼Demo
using （var context = new SqltestEntities（））
{
var result = context.GetUsersByDept（2）;
throw new Exception（result.Count（）.ToString（））;
}
工商網監
評論