3、执行 AFTER语句级触发器& v& g: c7 P A' s# i
2.2 创建DML触发器
8 [- [" _ F) X2 w$ W/ z2 L2 P 触发器名与过程名和包的名字不一样,它是单独的名字空间,因而触发器名可以和表或过程有相同的名字,但在一个模式中触发器名不能相同。' I; {7 k$ g% G' S2 S- ~
DML触发器的限制:# W0 l- w6 l% R$ S( c
CREATE TRIGGER语句文本的字符长度不能超过32KB;
: u: A4 |! c! p9 J4 t! ]' v 触发器体内的SELECT 语句只能为SELECT … INTO …结构,或者为定义游标所使用的SELECT 语句。4 x) s1 R8 y6 x/ j: F9 c
触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句;
. {, }/ a: l. U* U ^) B/ W3 h 由触发器所调用的过程或函数也不能使用数据库事务控制语句;9 s" U- Q. c3 T/ K7 }
触发器中不能使用LONG, LONG RAW 类型;
' p$ M3 a8 Z: c 发器内可以参照LOB 类型列的列值,但不能通过 :NEW 修改LOB列中的数据;
* e$ X7 D, R4 f/ M% m" ] DML触发器基本要点:/ O$ V# Q4 J) M/ W3 j) D8 f5 {
触发时机:指定触发器的触发时间。如果指定为BEFORE,则表示在执行DML操作之前触发,以便防止某些错误操作发生或实现某些业务规则;如果指定为AFTER,则表示在执行DML操作之后触发,以便记录该操作或做某些事后处理。6 n# [; y- n- B, M4 A( @7 }/ ^* \$ i
触发事件:引起触发器被触发的事件,即DML操作(INSERT、UPDATE、DELETE)。既可以是单个触发事件,也可以是多个触发事件的组合(只能使用OR逻辑组合,不能使用AND逻辑组合)。
8 x( H; c( k, j m4 \' ` 条件谓词:当在触发器中包含多个触发事件(INSERT、UPDATE、DELETE)的组合时,为了分别针对不同的事件进行不同的处理,需要使用ORACLE提供的如下条件谓词。
3 K) i" }& _/ z$ R: d 1)、INSERTING:当触发事件是INSERT时,取值为TRUE,否则为FALSE。
& ~: t! ]9 z) c; q7 J$ t, M3 Z9 z 2)、UPDATING [(column_1,column_2,…,column_x)]:当触发事件是UPDATE 时,如果修改了column_x列,则取值为TRUE,否则为FALSE。其中column_x是可选的。
9 O- }. q2 |6 B7 k8 y+ ` 3)、DELETING:当触发事件是DELETE时,则取值为TRUE,否则为FALSE。- H% f1 ]" P1 x* w" I, o) G. C
解发对象:指定触发器是创建在哪个表、视图上。
2 ?' g% ~* t/ \0 g3 a! \ 触发类型:是语句级还是行级触发器。
0 m/ H" G0 f0 P; L/ W 触发条件:由WHEN子句指定一个逻辑表达式,只允许在行级触发器上指定触发条件,指定UPDATING后面的列的列表。0 G0 ]- y8 T' L$ X
问题:当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值.4 ^2 G: |4 ^& X/ ]
实现: :NEW 修饰符访问操作完成后列的值$ V ]8 X& L% c7 ^) j
:OLD 修饰符访问操作完成前列的值/ R! j& M! N* J1 `7 E
特性2 \! ~6 p, U1 D0 \. Z* M
INSERT3 x9 l: H$ s; F( x- X0 Q8 B
UPDATE
/ v! ?+ X [. c# \! q5 a/ k DELETE
* s0 m8 f% F3 u1 N" R: a5 h OLD
/ i$ J& ~# z* ~! t NULL/ R& v, j$ ^7 t* C- n, ^
实际值
4 {# M* q0 |3 ~: S* h7 ? 实际值6 e, |1 Y/ ^$ B! _+ |, p4 x
NEW
S2 ~: Z! D5 _ 实际值: `6 M, u0 }: d( o9 v; Z
实际值
* y4 k- Q! |$ B+ O0 G' ?8 N' o; s NULL4 G/ U2 e6 p: l& w& N+ ^
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去。
1 }. \" l8 V! H0 T CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;$ `- B8 l3 ?4 u3 O3 ?( \% Y
CREATE OR REPLACE TRIGGER tr_del_emp$ L# s2 l. _+ M6 w4 X& z1 W
BEFORE DELETE --指定触发时机为删除操作前触发( k) m) ]5 |$ n& r+ ]
ON scott.emp
$ e/ e' f% H, c$ D& \5 i FOR EACH ROW --说明创建的是行级触发器
- Q; K; W9 T6 m( ^# M, Q BEGIN" E$ T/ r! q, B
--将修改前数据插入到日志记录表 del_emp ,以供监督使用。9 O- h# X6 Q$ p0 P
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
! F9 R* s6 z- Q0 V0 n9 n* F4 w VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
4 a: N# k) S0 c' U+ N" o END;$ Q/ A/ s- [- W+ k+ t9 X5 V0 M
DELETE emp WHERE empno=7788;9 \0 Y# L0 T% Z. L; M) u, R, G
DROP TABLE emp_his;6 R1 r( _# y* u! i" A
DROP TRIGGER del_emp; 例2:限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围,即不允许在非工作时间修改departments表。$ A; y' C$ b) R% J" S
CREATE OR REPLACE TRIGGER tr_dept_time
- s: G5 P& [7 L( f) g }: V& \$ w BEFORE INSERT OR DELETE OR UPDATE `3 Q! A; _ Q( r
ON departments) [- t1 d+ Y& x( Z" u- o
BEGIN
7 p. \0 g# F: Z3 q IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
) Z9 y3 K; X! I: H* j. N& P9 V4 p0 ~ RAISE_APPLICATION_ERROR(-20001, '不是上班时间,不能修改departments表');) f% _1 z$ f( n: Y2 w
END IF; o, r$ f! F A# X. X+ L1 ?
END; 例3:限定只对部门号为80的记录进行行触发器操作。
# w$ l9 n& ~" r6 u& M' G# j! h- S CREATE OR REPLACE TRIGGER tr_emp_sal_comm
, _! |$ j, T( g4 D _9 A$ j BEFORE UPDATE OF salary, commission_pct
9 ? g5 C) z/ n+ Q OR DELETE! W0 N( J9 w( @' N/ s
ON HR.employees
' v% i: g, w3 Z3 H* ~ FOR EACH ROW
) p' R6 Q8 K6 i/ L; F WHEN (old.department_id = 80)# Y, t5 B4 Y! \1 f9 p0 N
BEGIN$ N0 A) `. q4 u5 `7 [: ^: |& i( s
CASE3 |2 ~- h' V4 B, r. e t N
WHEN UPDATING ('salary') THEN% \2 w, L" B/ w! ]7 O/ o$ e& p. O
IF :NEW.salary < :old.salary THEN
3 k5 m* P2 L* i* c" U RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');& Z: s- C3 W+ K2 P- M
END IF;
, c" }$ |( ?5 T V WHEN UPDATING ('commission_pct') THEN6 ^ k/ f. l8 Y) U$ ?4 D0 i
IF :NEW.commission_pct < :old.commission_pct THEN0 M* w: q7 Y- s
RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');
$ @" {4 a4 \. x( v: B! \- n# } END IF;
# T: E, I1 g7 k& ]! y$ j/ A- A WHEN DELETING THEN: N) H( N3 z& o+ ]/ Q. _ _
RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');: d8 [" ?/ E- ^1 Z& i* Q
END CASE;
+ N" M8 e% Z" e7 C, q END;
, w B' b6 D3 q$ @) b, X+ D) E9 @ /* |