Oracle 之 觸發器

標籤：io   ar   os   使用   for   sp   strong   資料   on   

　　觸發器是特定的事件出現的時候，自動隱式執行代碼塊，這個過程使用者無法控制，使用者只能控制觸發的事件，觸發後的操作，觸發過程是自動執行的。

定義觸發器：

　　1、名稱

　　2、觸發時間：是在執行事件之前(before)觸發,還是之後(after)觸發

　　3、事件：insert(插入)、delete(刪除)、update(更新)

　　4、作用目標：on table_name

　　5、引用的值：old(舊值)、new(新值)

　　6、範圍：for each row（行級）或 無此行表示語句級

　　7、約束條件：可選，where 約束條件，不能包含查詢語句，也不能調用PL/SQL函數

　　8、主體語句：begin ...

　　樣本：create or replace trigger biufer_employess_emp_id

　　　　　  before insert or update of emp_id on employees

　　　　　　referencing old as old_value new as new_value

　　　　　　for each row

　　　　　　when (new_value.emp_id =9)

　　　　　　begin

　　　　　　　:new_value.age :=26;

　　　　　end biufer_employess_emp_id;

　　　當執行下面SQL語句時觸發器被觸發：

　　　INSERT INTO employees(emp_id,emp_name,position,age,address)
       　　VALUES(9,‘周迅‘,‘開發工程師‘,NULL,‘南京路99號‘);

　　　觸發器自動執行，不會通知使用者，再次查看employees表時：

　　　　9 周迅 開發工程師 26 南京路99號 

　　　但此時觸發器不會提交，觸發器裡也不能使用commit/rollback/DDL語句，只能由使用者提交（或其他程式）

觸發器類型

　1、語句觸發器

　　在表上或者某些情況下的視圖上執行的特定語句或者語句組的觸發器。能夠與insert、delete、update或者組合上進行關聯。但無論使用什麼樣的組合，各個語句觸發器都只會針對指定語句啟用一次。比如，無論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表

　2、行觸發器

　　是指為受到影響的各個行啟用的觸發器。

　　樣本：為主鍵產生自增序號

　　create table foo(id number,data varchar2(20));

　　create sequence foo_seq;

　　create or replace trigger bifer_foo_pk

　　before insert on foo

　　for eache row

　　begin

　　　select foo_seq.nextval into :new.id from dual;

　　end

　　SQL語句

　　insert into foo(data) values(‘donny‘);//自動添加id值

　　insert into foo values(5,‘chen‘);　　//插入該行5會被替換成foo_seq.nextval產生的值

　　select *from foo;

　3、instead of 觸發器

　　create or replace view company_name as select emp_id,emp_name,position,age,address from scctt.employess;

　　update company_name set emp_name = ‘tom‘ where emp_id = 13;

　　create or replace trigger update_name_company_name

　　　　instead of update on company_name

　　begin

　　　　update employess set emp_id := :new.emp_id,

　　　　emp_name := :new.emp_name,

　　　　where employess_id = :old.emp_id;

　　end;　　

　4、系統條件觸發器

　　系統事件：資料庫啟動、關閉，伺服器錯誤

　　create trigger ad_startup  after startup on database

　　begin

　　---do some stuff

　　end;

　5、使用者事件觸發程序　

　　使用者事件：使用者登入、登出，create/alter/drop/analyze/audit/grant/revoke/rename/trancate/logoff

　　　樣本：記錄刪除對象

　　create table drop_object(obj_name varchar2(30),obj_type varchar2(30),dropped_on date);　　//日誌表

　　create or replace trigger log_drop_trigger　　//　觸發器

　　before drop on donny.schema

　　begin

　　insert into drop_object values(ora_dict_obj_name,ora_dict_type,sysdate);

　　end;

　　SQL語句：

　　create table drop_me(a number);

　　create view drop_me_view as select *from drop_me;

　　drop view drop_me_view;

　　drop table drop_me;

　　select *from droped_objects

　　禁用和啟用觸發器 
　　　　alter trigger <trigger_name> disable; 
　　　　alter trigger <trigger_name> enable;

　　觸發器的作用

　　　　1、允許/限制對錶的修改

　　　　2、自動產生衍生的資料行，比如自增欄位。

　　　　3、強制資料一致性。

　　　　4、提供審計和日誌記錄

　　　　5、防止無效的交易處理

　　　　6、啟用複雜的商務邏輯

 

　　　　　　

Oracle 之 觸發器