觸發器組成:
觸發事件：即在何種情況下觸發TRIGGER; 例如：INSERT, UPDATE, DELETE。
觸發時間：即該TRIGGER 是在觸發事件發生之前（BEFORE）還是之後(AFTER)觸發，也就是觸發事件和該TRIGGER 的操作順序。
觸發器本身：即該TRIGGER 被觸發之後的目的和意圖，正是觸發器本身要做的事情。 例如：PL/SQL 塊。
觸發頻率：說明觸發器內定義的動作被執行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。
語句級(STATEMENT)觸發器：是指當某觸發事件發生時，該觸發器只執行一次；
行級(ROW)觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行數據，觸發器都單獨執行一次。
創建觸發器的一般語法是:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON {[schema.] table_name | [schema.] view_name}
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;
其中：
BEFORE 和AFTER :
指出觸發器的觸發時序分別為前觸發和後觸發方式，前觸發是在執行觸發事件之前觸發當前所創建的觸發器，
後觸發是在執行觸發事件之後觸發當前所創建的觸發器。
INSTEAD OF :
選項使ORACLE激活觸發器，而不執行觸發事件。只能對視圖和對象視圖建立INSTEAD OF觸發器，
而不能對表、模式和數據庫建立INSTEAD OF 觸發器。
FOR EACH ROW :
選項說明觸發器為行觸發器。行觸發器和語句觸發器的區別表現在：行觸發器要求當一個DML語句操走影響
數據庫中的多行數據時，對于其中的每個數據行，只要它們符合觸發約束條件，均激活一次觸發器；而語句觸發器將整個語句
操作作為觸發事件，當它符合約束條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句
觸發器，而INSTEAD OF 觸發器則為行觸發器。
REFERENCING :
說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照當前的新、舊列值，
默認的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。
WHEN :
說明觸發約束條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，
也不能調用PL/SQL 函數。WHEN 子句指定的觸發約束條件只能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF
行觸發器和其它類型的觸發器中。
當一個基表被修改( 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觸發器觸發次序
1. 執行 BEFORE語句級觸發器;
2. 對與受語句影響的每一行：
執行 BEFORE行級觸發器
執行 DML語句
執行 AFTER行級觸發器
3. 執行 AFTER語句級觸發器
觸發器的限制
CREATE TRIGGER語句文本的字符長度不能超過32KB；
觸發器體內的SELECT 語句只能為SELECT … INTO …結構，或者為定義遊標所使用的SELECT 語句。
觸發器中不能使用數據庫事務控制語句 COMMIT; ROLLBACK, SVAEPOINT 語句；
由觸發器所調用的過程或函數也不能使用數據庫事務控制語句；
觸發器中不能使用LONG, LONG RAW 類型；
觸發器內可以參照LOB 類型列的列值，但不能通過 :NEW 修改LOB列中的數據；
觸發器所訪問的表受到表的約束限制，即後面的“變化表”。
問題：當觸發器被觸發時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、 後列的值.
實現: :new 修飾符訪問操作完成後列的值
:old 修飾符訪問操作完成前列的值
特性 INSERT UPDATE DELETE
OLD NULL 有效 有效
NEW 有效 有效 NULL
功能：
1、 允許/限制對表的修改
2、 自動生成派生列，比如自增字段
3、 強制數據一致性
4、 提供審計和日志記錄
5、 防止無效的事務處理
6、 啟用復雜的業務邏輯
開始
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
觸發器的組成部分：
1、 觸發器名稱
2、 觸發語句
3、 觸發器限制
4、 觸發操作
1、 觸發器名稱
create trigger biufer_employees_department_id
命名習慣：
biufer（before insert update for each row）
employees 表名
department_id 列名
2、 觸發語句
比如：
表或視圖上的DML語句
DDL語句
數據庫關閉或啟動,startup shutdown 等等
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
說明：
1、 無論是否規定了department_id ，對employees表進行insert的時候
2、 對employees表的department_id列進行update的時候
3、 觸發器限制
when (new_value.department_id<>80 )
限制不是必須的。此例表示如果列department_id不等于80的時候，觸發器就會執行。
其中的new_value是代表更新之後的值。
4、 觸發操作
是觸發器的主體
begin
:new_value.commission_pct :=0;
end;
主體很簡單，就是將更新後的commission_pct列置為0
觸發：
insert into employees(employee_id,
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);
select commission_pct from employees where employee_id=12345;
觸發器不會通知用戶，便改變了用戶的輸入值。
觸發器類型：
1、 語句觸發器
2、 行觸發器
3、 INSTEAD OF 觸發器
4、 系統條件觸發器
5、 用戶事件觸發器
1、 語句觸發器
是在表上或者某些情況下的視圖上執行的特定語句或者語句組上的觸發器。能夠與INSERT、UPDATE、DELETE或者組合上進行關聯。但是無論使用什麼樣的組合，各個語句觸發器都只會針對指定語句激活一次。比如，無論update多少行，也只會調用一次update語句觸發器。
例子：
需要對在表上進行DML操作的用戶進行安全檢查，看是否具有合適的特權。
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;
/
即使SYS，SYSTEM用戶也不能修改foo表
[試驗]
對修改表的時間、人物進行日志記錄。
1、 建立試驗表
create table employees_copy as select *from hr.employees
2、 建立日志表
create table employees_log(
who varchar2(30),
when date);
3、 在employees_copy表上建立語句觸發器，在觸發器中填充employees_log 表。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(
Who,when)
Values( user, sysdate);
End;
/
4、 測試
update employees_copy set salary= salary*1.1;
select *from employess_log;
5、 確定是哪個語句起作用？
即是INSERT/UPDATE/DELETE中的哪一個觸發了觸發器？
可以在觸發器中使用INSERTING / UPDATING / DELETING 條件謂詞，作判斷：
begin
if inserting then
-----
elsif updating then
-----
elsif deleting then
------
end if;
end;
if updating(‘COL1’) or updating(‘COL2’) then
------
end if;
[試驗]
1、 修改日志表
alter table employees_log
add (action varchar2(20));
2、 修改觸發器，以便記錄語句類型。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%ype;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log(
Who,action,when)
Values( user, l_action,sysdate);
End;
/
3、 測試
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select *from employees_log
全站熱搜
留言列表
ORACLE JDeveloper (1)