一.觸發(fā)器是什么
觸發(fā)器是數(shù)據(jù)庫中,隱含被執(zhí)行的存儲(chǔ)過程存儲(chǔ)過程,當(dāng)發(fā)生特定的事件時(shí),orcle會(huì)自動(dòng)執(zhí)行觸發(fā)器相對應(yīng)的代碼.
二.觸發(fā)器的分類
1.DML觸發(fā)器
2.DDL觸發(fā)器
3.INSTEAD 觸發(fā)器
4.事件觸發(fā)器
三.例子
CREATE OR REPLACE TRIGGER upd_esp_category
BEFORE INSERT OR UPDATE
OF email
ON ex_edm_task_detail
FOR EACH ROW
DECLARE
v_category VARCHAR2(64);
BEGIN
SELECT esp_name INTO v_category
FROM ex_edm_esp
WHERE mail_suffix=SUBSTR(:new.email,INSTR(:new.email,'@',1,1)+1, LENGTH(:new.email));
:new.category := v_category;
EXCEPTION
WHEN no_data_found THEN
:new.category := '未知';
WHEN too_many_rows THEN
:new.category := '未知';
WHEN OTHERS THEN
:new.category := '未知';
END;再來一個(gè)例子:
create or replace trigger wf_tri_user_list before insert or update or delete on user_list
for each row
declare
uid varchar2(10); useq varchar2(10); asql varchar2(200); namea varchar2(200); nameb varchar2(200);
begin
namea:=NULL;
nameb:=NULL;
if inserting then
insert into wflow.bpm_org_user(userid,username,diaplayname,seq) values(:NEW.user_id,:NEW.user_name,:NEW.user_realname,:NEW.user_id);
dbms_output.put_line('insert trigger is chufale 
..');
end if;
if updating then
if (:NEW.user_name<>:OLD.user_name) and (:NEW.user_realname<>:OLD.user_realname) then
namea:=:NEW.user_name;
nameb:=:NEW.user_realname;
asql:='update wflow.bpm_org_user set diaplayname=:1 where username=:2';
execute immediate asql using namea,nameb;
else
if :NEW.user_name<>:OLD.user_name then
namea:=:NEW.user_name;
asql:='update wflow.bpm_org_user set user_name=:1 where username=:2';
execute immediate asql using namea;
else
if :NEW.user_realname<>:OLD.user_realname then
nameb:=:NEW.user_realname;
asql:='update wflow.bpm_org_user set diaplayname=:1 where username=:2';
execute immediate asql using nameb,:OLD.user_id;
end if;
end if;
end if;
end if;
if deleting then
update wflow.bpm_org_jobusers set userid = 0 where :OLD.user_id =userid and parentid=-1;
delete from wflow.bpm_org_jobusers where userid = :OLD.user_id;
delete wflow.bpm_org_user where userid=:OLD.user_id;
end if;
commit;
end;
:NEW 和:OLD使用方法和意義，new 只出現(xiàn)在insert和update時(shí)，old只出現(xiàn)在update和delete時(shí)。在insert時(shí)new表示新插入的行數(shù)據(jù)，update時(shí)new 表示要替換的新數(shù)據(jù)、old表示要被更改的原來的數(shù)據(jù)行，delete時(shí)old表示要被刪除的數(shù)據(jù)。
注意：
在觸發(fā)器中不能使用commit。