DML触发器基本要点:
2 e1 E |8 {0 D5 {6 j 触发时机:指定触发器的触发时间。如果指定为BEFORE,则表示在执行DML操作之前触发,以便防止某些错误操作发生或实现某些业务规则;如果指定为AFTER,则表示在执行DML操作之后触发,以便记录该操作或做某些事后处理。6 U5 d2 Q- r: u4 E
触发事件:引起触发器被触发的事件,即DML操作(INSERT、UPDATE、DELETE)。既可以是单个触发事件,也可以是多个触发事件的组合(只能使用OR逻辑组合,不能使用AND逻辑组合)。
: N. e; y7 U# o" d1 r W! O 条件谓词:当在触发器中包含多个触发事件(INSERT、UPDATE、DELETE)的组合时,为了分别针对不同的事件进行不同的处理,需要使用ORACLE提供的如下条件谓词。
7 B* }5 r* @- Q" _/ g 1)、INSERTING:当触发事件是INSERT时,取值为TRUE,否则为FALSE。
0 S, y' w3 ^7 u. g/ m 2)、UPDATING [(column_1,column_2,…,column_x)]:当触发事件是UPDATE 时,如果修改了column_x列,则取值为TRUE,否则为FALSE。其中column_x是可选的。2 o6 y" Z8 O$ e- B; O1 n" m
3)、DELETING:当触发事件是DELETE时,则取值为TRUE,否则为FALSE。3 X, p* E/ Y! _ @; {
解发对象:指定触发器是创建在哪个表、视图上。- t/ T: l: S0 }; s
触发类型:是语句级还是行级触发器。7 P Z, u3 u* [ z
触发条件:由WHEN子句指定一个逻辑表达式,只允许在行级触发器上指定触发条件,指定UPDATING后面的列的列表。
. D" X4 a/ \' j; {2 w' h1 \ 问题:当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值.
& f' I& g/ z# t7 q: z1 x8 j7 D1 a 实现: :NEW 修饰符访问操作完成后列的值
- \) t) o* c. k; V0 `/ n :OLD 修饰符访问操作完成前列的值
0 J7 k: C+ \0 S 特性1 ] z& P% ^' N9 c3 P3 O
INSERT; v: ~/ j# R2 Y: v4 S
UPDATE
/ V7 j7 ]) Q' t DELETE" o6 d+ z8 T8 V0 f
OLD
6 {3 {/ @+ j6 q7 C4 F! Y- r7 q NULL
4 E3 U, n W7 [' a: F( G' F c 实际值/ X) g2 E% Z+ R4 O
实际值" \" N0 i0 U" Z# d. J- b8 z, s
NEW
" ~7 X6 }# a- w( X 实际值) K% Z; H% z* w, V
实际值
+ D* U x" ]) g7 Q4 O; Y NULL: } h; v {- u$ c: R) M+ c) }
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去。
8 g9 w' n2 x) L5 }2 m CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
9 y6 X o$ e Y; \& E1 m: y CREATE OR REPLACE TRIGGER tr_del_emp2 K- A$ C2 n/ t' P/ a
BEFORE DELETE --指定触发时机为删除操作前触发1 U, S0 h; D$ C H
ON scott.emp, e& ~6 Y0 H# ?* ]
FOR EACH ROW --说明创建的是行级触发器
7 H% c! k2 p& W. D BEGIN
# x1 i. x" _ E8 d* T --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
- W* \. n: }( `- m) p INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ). Z" J2 @, ]3 r- i; j7 P
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
; _, E9 p1 X3 K0 L END;/ s5 |9 K. \ m6 U0 Q; i' l8 J% J
DELETE emp WHERE empno=7788;
+ y% K6 X& N1 v4 P/ T# u# c DROP TABLE emp_his;4 x4 a4 I7 m& H2 d) q" A' [, q ~2 w
DROP TRIGGER del_emp; 例2:限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围,即不允许在非工作时间修改departments表。& T$ G7 S6 Z4 c3 i$ V) x; S- R! m
CREATE OR REPLACE TRIGGER tr_dept_time. A( z n, r/ ^( B9 @
BEFORE INSERT OR DELETE OR UPDATE
$ ~, l! T" n+ ~! l5 D0 M7 o% k7 A/ A ON departments
3 o7 g S# f) @5 D7 _ BEGIN
% ?; N6 f) A/ q' h' ` IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN: i1 w8 l, T% h0 G, T4 S8 s
RAISE_APPLICATION_ERROR(-20001, '不是上班时间,不能修改departments表');
* c2 t+ R) U4 n9 g/ f" o! c* w END IF;: N1 v+ [: t6 F9 d" g7 j* n
END; 例3:限定只对部门号为80的记录进行行触发器操作。
) V. q) X/ h, d/ S; p M4 A# w8 o2 H CREATE OR REPLACE TRIGGER tr_emp_sal_comm5 M1 r9 L& P( W" O$ y
BEFORE UPDATE OF salary, commission_pct4 ] \5 u( ^7 ^) ~* d0 f
OR DELETE2 k4 n7 }% |! A4 Q" M
ON HR.employees
7 B u$ o l# @& s FOR EACH ROW
e$ r1 T6 H0 ^ S2 }8 k WHEN (old.department_id = 80)
1 ` G. E, h M: k% J b) } BEGIN& N7 A( }0 k7 d
CASE
3 v D) w/ A2 x i5 H6 O$ q WHEN UPDATING ('salary') THEN
, C6 F3 c/ v, W6 z IF :NEW.salary < :old.salary THEN- o9 a& F. y3 e$ N' j0 `, O, Y3 H
RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');
* g0 _2 y* [; ~2 Z4 r' k END IF; w# U) h' o0 |0 r. P! q" l
WHEN UPDATING ('commission_pct') THEN
8 B: O$ I& b6 Y0 V0 X IF :NEW.commission_pct < :old.commission_pct THEN* }' }3 z1 v) d4 c7 P7 h
RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');
0 G } Y& O$ r8 M' w END IF;7 K8 m& u8 t6 G4 A: @
WHEN DELETING THEN. f9 \; X6 E7 ]' A4 \% f. [0 J" f0 `
RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');
Z5 |; j# i. j0 g, b! A END CASE;
. \( r' ^# }" ?" _' I2 j END;
0 E) j! P( [) u9 c: k; R /* </p> |