Oracle觸發器詳細介紹

是特定事件出現的時候，自動執行的代碼塊。類似于 存儲 過程，但是用戶不能直接調用他們。

　　功能：

　　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、 用戶事件觸發器

是在表上或者某些情況下的視圖上執行的特定語句或者語句組上的觸發器。能夠與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%type;

　　?????? 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

建表：

create table User_Info (
?? ID?????????????????? INTEGER???????????????????????? not null,
?? UserName??????????? VARCHAR(30)???????????????????? not null,
?? PassWord??????????? VARCHAR(20)???????????????????? not null,
?? CreateDate????????? Date??????????????????????????? not null,
?? Status????????????? INTEGER???????????????????????? not null,
?? constraint PK_User_Info primary key (ID)
);

create table User_Info_temp (
?? ID?????????????????? INTEGER???????????????????????? not null,
?? UserName??????????? VARCHAR(30)???????????????????? not null,
?? PassWord??????????? VARCHAR(20)???????????????????? not null,
?? CreateDate????????? Date??????????????????????????? not null,
?? Status????????????? INTEGER???????????????????????? not null,
?? constraint PK_User_Info_temp primary key (ID)
);

觸發器寫法：

create or replace trigger UserToTemp after insert or update or delete
on user_info for each row
declare
??? integrity_error exception;
??? errno??????????? integer;
??? errmsg?????????? char(200);
??? dummy??????????? integer;
??? found??????????? boolean;
???
begin
if inserting then
??? insert into User_info_temp(ID,UserName,PassWord,CreateDate,Status) values(:NEW.ID,:NEW.UserName,:NEW.PassWord,:new.CreateDate,:NEW.Status);
elsif updating then
??? update User_info_temp set ID=:NEW.ID,UserName=:NEW.UserName,PassWord=:NEW.PassWord,Status=:NEW.Status where id=:OLD.id;
elsif deleting then
??? delete from User_info_temp where id=:OLD.id;
end if;
exception
??? when integrity_error then
?????? raise_application_error(errno, errmsg);
end;

測試數據：
insert into user_info(ID,UserName,PassWord,CreateDate,Status)values(1,'xier','222',to_date('2008-10-11','yyyy-mm-dd'),1)

update user_info u set u.status=3,u.username='xier' where u.id=1

delete from user_info u where u.id=1

Oracle觸發器詳細介紹